topic Re: Yesterday as a variable in a stored procedure in QlikView

Yesterday as a variable in a stored procedure

mgavidia — Sat, 11 May 2013 00:12:01 GMT

Hello everyone,

I hope someone show me what I am doing wrong here. I have perfomed my research on this forum but I still cannot make it work...

How can I pass yesterday as a variable on a Stored Procedure? I need to capture yesterday's data using the SP below.

SQL EXEC SPR_GetDailyRankingMoveIn '$(vToday())', '1,2,3'; (This returns Today data)(This works fine, it returns Today's data)

Base on what I have seen here, I've modified it as follows but it doesn't work.

SQL EXEC SPR_GetDailyRankingMoveIn '$Date(Today(1) -1', '1,2,3'; (This one fails)

I get this error message:

SQL##f - SqlState: 22008, ErrorCode: 241, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

How should the parameter for yesterday needs to be entered so my SP can return yesterday's data?

Thanks,

Miguel Gavidia

Re: Yesterday as a variable in a stored procedure

Gysbert_Wassenaar — Sat, 11 May 2013 07:32:59 GMT

LET vYesterday = today() -1;

SQL EXEC SPR_GetDailyRankingMoveIn '$(vYesterday)', '1,2,3';

You may have to give vYesterday the date format your SQL database expects. In that case use the date function to add a date format string to format the date. Something like vYesterday = date(today()-1, 'YYYY-MM-DD');

Re: Yesterday as a variable in a stored procedure

mgavidia — Sun, 12 May 2013 00:13:22 GMT

Gysbert,

Thank you for your reply.

I was working it out with vYesterday, as you have it above, the problem was that

I was missing the format on my vYesterday variable.

I am using vYesterday with the right format and my SP is working fine now.

Again, thank you very much for your help.

Miguel Gavidia