'DATE' is not a recognized built-in function name
-
Please assist. I am getting the above error:
CREATE VIEW DailyAccountBalances AS
SELECT
DATE(TransactionDate) AS Date,
SUM(CASE WHEN TransactionType = 'Opening Balance' THEN Amount ELSE 0 END) AS OpeningBalance,
SUM(CASE WHEN TransactionType = 'Closing Balance' THEN Amount ELSE 0 END) AS ClosingBalance,
SUM(CASE WHEN TransactionType = 'Debit' THEN Amount ELSE 0 END) AS DebitedAmount,
SUM(CASE WHEN TransactionType = 'Credit' THEN Amount ELSE 0 END) AS CreditedAmount
FROM Transactions
GROUP BY DATE(TransactionDate);
-------------- -
DATE is a DATATYPE not a function.
I am guessing you want to convert a DATETIME into a DATE value so you need the CONVERT function
CONVERT(DATE,TransactionDate) AS [Date]
If not please detail what you are trying to do with the TransactionDate column and the appropriate function can be detailed.
-
or instead use the ANSI style CAST:
CAST(TransactionDate AS DATE)
I know that some SQL databases support a DATE function, but SQL Server doesn't. You have to use CAST or CONVERT instead.
-
I would like a Daily aggregated table or data view that tracts the opening and closing balances of accounts
along with debited and credited amounts. The data view needs to be able to roll up to a
daily level that will be used to track the total opening and closing balance of ALL accounts.
-
-
You miss a comma after the CONVERT/CAST line - and you still need to change the GROUP BY.
-
CREATE VIEW DailyAccountBalances AS
SELECT
CONVERT(DATE, TransactionDate) AS Date,
SUM(CASE WHEN TransactionType = 'Opening Balance' THEN Amount ELSE 0 END) AS OpeningBalance,
SUM(CASE WHEN TransactionType = 'Closing Balance' THEN Amount ELSE 0 END) AS ClosingBalance,
SUM(CASE WHEN TransactionType = 'Debit' THEN Amount ELSE 0 END) AS DebitedAmount,
SUM(CASE WHEN TransactionType = 'Credit' THEN Amount ELSE 0 END) AS CreditedAmount
FROM Transactions
GROUP BY CONVERT(DATE, TransactionDate); -
Thanks for the assistance. I tried both and nothing yet:
-
Well, the cast2.png says it clearly. The column name is not found in the Transactions table/view.
So now you have to find out what the column you stated as being named TransactionDate (in your question) is really named (or if you forgot to include it in the view (if Transactions is a view).
-
Looks like you need to go and look at your schema.
All that red underlines mean you don’t have the right table or columns in your query.
Go look at the tables and substitute the correct table and columns into the query.
-
Ant-Green is correct, something is not right.
Are you connected to the correct server? Have you selected the correct database, before firing off the statement? I see no USE statement at the top, so you'd have to select the database from the drop-down list in SSMS.
-
Make sure you are in the correct database - put a USE statement and a GO before the CREATE VIEW:
USE {your database here};
GO
CREATE VIEW ...
...
GOAnd - please do yourself a favor and schema-qualify the table (e.g. dbo.Transactions) and add a table alias (e.g. FROM dbo.Transactions AS t). Then use the alias to reference the columns in your query - this way you won't have issues when you have to add another table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
DDL of Transactions table and ransactions_type below. I tried changing the column name.
/****** Object: Table [dbo].[Transactions] Script Date: 2023/01/31 22:10:46 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transactions](
[transaction_id] [bigint] NOT NULL,
[date] [date] NULL,
[primary_account_id] [int] NULL,
[parent_account_id] [int] NULL,
[provider_account_id] [int] NULL,
[icp_account_id] [int] NULL,
[group_id] [int] NULL,
[primary_type] [int] NULL,
[transaction_amount] [decimal](18, 2) NULL,
[stock_cost] [decimal](18, 2) NULL,
[vat] [decimal](18, 2) NULL,
[superdealer_vat_reg] [decimal](18, 2) NULL,
[vendor_vat_reg] [int] NULL,
[vendor_comm] [decimal](18, 2) NULL,
[superdealer_comm] [decimal](18, 2) NULL,
[icp_comm] [decimal](18, 2) NULL,
[psitek_comm] [decimal](18, 2) NULL,
[vendor_rbc_cost] [decimal](18, 2) NULL,
[bank_charge] [decimal](18, 2) NULL,
[bank_charge_owner] [decimal](18, 2) NULL,
[detail_type_name] [varchar](50) NULL,
[detail_object_id] [bigint] NULL,
[primary_account_balance] [decimal](18, 2) NULL,
[content_type] [int] NULL,
[reversal_id] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
[transaction_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMAR-------------
USE [Kazang]
GO
/****** Object: Table [dbo].[transaction_type01] Script Date: 2023/01/31 22:15:47 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[transaction_type01](
[transaction_type] [nvarchar](255) NULL,
[transaction_code] [int] NULL
) ON [PRIMARY]
GO -
What's the relationship between the two tables? Are they to be joined, and if yes, on which columns?
-
Yes.
Transection_type.transaction_code joins on Transactions.primary_type.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply