Yesterday as a variable in a stored procedure

[mgavidia]

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

[Gysbert_Wassenaar · Accepted solution]

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

[mgavidia]

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