I'm trying to automate the starting/cutoff point in my SQL using a reference to a variable created in my script.
1. In my variables section, I have created a 'Let' variable that generates a date based on the report reload time. I've called this variable vLastQtr. It currently generates a date of 2/1/2012 (February 1, 2012) using the MakeDate function. I have reviewed the variable and am satisfied that it generates the correct date.
2. In my SQL query, I am pulling data where the date >= '2/1/2012'. In place of the '2/1/2012', I tried substituting $(vLastQtr), thinking that it would compare against the 2/1/2012 date generated by that variable. The script errors out with a message of, "inconsistent datatypes: expected DATE got NUMBER."
Is it possible to substitute a variable in place of the date in SQL? If so, how do I correct the data type consistency, so the SQL will recognize it properly?
Re: Automate Report Start Date in SQL Using Variable?
Thanks, Kaushik. I tried your solution, but experienced the following issues:
If I use single quotes ( ' ') around the '$(vDate)' in my SQL, it fetches zero records. The syntax check gives the appearance that it does not recognize it as a variable (not italicized or in gray like other variables. Example: $(vLastQtrDate6).
If I do not use single quotes $(vDate) for my date criteria, the script returns an error that an expression is missing. When I scroll to the date portion of the where clause in the SQL statement, the date is blank.
Any other thoughts? I suspect that using a variable will work. I just don't have the right date format. I would send a QV sample, but it would be time-consuming to recreate.
Should I perhaps use single quotes in place of either of the double quotes in the let vDate formula?