Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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())
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
you can use a variable in Qlikview
Set vYearToday = Year(Today());
An then in your clause SQL
WHERE Year(payments.paymentdate) <= $(vYearToday);
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
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);
Thank you Jonathan! Passing the SQL GetDate() worked.
Ok Sorry, I made a mistake, you have to put Let and not Set.
But the Jonathan Solution is better
No problem. I now know multiple ways to address situations like this. Thank you for the insight