topic Re: 'Today' is not a recognized built-in function name in QlikView

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

asmithids — Fri, 05 Dec 2014 14:22:21 GMT

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())

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

yduval75 — Fri, 05 Dec 2014 14:24:33 GMT

you can use a variable in Qlikview

Set vYearToday = Year(Today());

An then in your clause SQL

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

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

jonathandienst — Fri, 05 Dec 2014 14:34:12 GMT

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

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

asmithids — Fri, 05 Dec 2014 14:59:28 GMT

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);

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

asmithids — Fri, 05 Dec 2014 15:51:29 GMT

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

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

yduval75 — Fri, 05 Dec 2014 16:01:42 GMT

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

But the Jonathan Solution is better

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

asmithids — Fri, 05 Dec 2014 16:29:20 GMT

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