Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
asmithids
Partner
Partner

'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
Partner

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
Partner

you can use a variable in Qlikview

Set vYearToday = Year(Today());

An then in your clause SQL

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

jonathandienst
Partner
Partner

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
Partner
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
Partner
Author

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

yduval75
Partner
Partner

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

But the Jonathan Solution is better

asmithids
Partner
Partner
Author

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