3 Replies Latest reply: Mar 29, 2013 1:48 AM by Kaushik Solanki RSS

    Automate Report Start Date in SQL Using Variable?

      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?
          Kaushik Solanki



               Try to assign the variable in this way.


               let vDate = '"'&Text(Date(MakeDate(2013,02,01),'DD-MMM-YYYY'))&'"';


               Sql Select * from abc where date >= '$(vDate)';




               Sql Select * from abc where date >= $(vDate);



          Kaushik Solanki

            • 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?