Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asmithids
Partner - Creator II
Partner - Creator II

'Today' is not a recognized built-in function name

Hello,

I have the following load statement that is failing to load. 

LOAD 0 as PriceShipping, 0 as PriceTax, 'ValidationNumber' as ValidationNumber, 'TransactionNumber' as TransactionNumber,'Status' as Status, Num(paymentdate) as paymentdate , orderid, paymentamount, paymenttype, employee, '022' as Store#, 'Corporate' as StoreType;
SQL SELECT * FROM "022".dbo.payments WHERE Year(payments.paymentdate) <= Year(Today());

I am getting the following error that the 'Today' function is not a recognized built-in function name.  I'm not sure why as the Today() is a valid built-in function.  Thank you in advance for any assistance on this issue. 

SQL##f - SqlState: 37000, ErrorCode: 195, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server] 'Today' is not a recognized built-in function name.

SQL SELECT * FROM "022".dbo.payments WHERE Year(payments.paymentdate) <= Year(Today())

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

The code after the SQL statement is executed on the DB server, not in Qlikview. You can use GetDate() for SQL server, (or the equivalent function for any other DBMS) or you can pass in a variable from QV as suggested above.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
yduval75
Partner - Creator III
Partner - Creator III

you can use a variable in Qlikview

Set vYearToday = Year(Today());

An then in your clause SQL

WHERE Year(payments.paymentdate) <= $(vYearToday);

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

The code after the SQL statement is executed on the DB server, not in Qlikview. You can use GetDate() for SQL server, (or the equivalent function for any other DBMS) or you can pass in a variable from QV as suggested above.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
asmithids
Partner - Creator II
Partner - Creator II
Author

Yoann,

Thank you for your response.  I created the variable and added it to the load script below.  However, I am still getting the exact same error using the variable. 

SQL SELECT * FROM "022".dbo.payments WHERE Year(payments.paymentdate) <= $(vYearToday);

asmithids
Partner - Creator II
Partner - Creator II
Author

Thank you Jonathan!  Passing the SQL GetDate() worked. 

yduval75
Partner - Creator III
Partner - Creator III

Ok Sorry, I made a mistake, you have to put Let and not Set.

But the Jonathan Solution is better

asmithids
Partner - Creator II
Partner - Creator II
Author

No problem.  I now know multiple ways to address situations like this.  Thank you for the insight