Passing single quotes to a variable - used in SQL where statement

Anonymous — Tue, 07 May 2013 04:50:35 GMT

Problem: I need to store a date in a variable used in SQL Where statement but the single quotes around the date do not come through.

I have a variable that pulls a date used as the earliest date I want to pull data for.

tStartDate:
LOAD 
     max(DataStartDt) as DataStartDt
FROM
Calendar.qvd
(qvd);
Let vStartDate = Peek('DataStartDt');

in this case vStartDate = 40544 (translating to the date 1/1/2011)

-----------------------------------

The date is then used in the vIncrementalExpression which holds the Where in my SQL statement.

LET vIncrementalExpression = 'WHERE dataDate >=' & date($(vStartDate), 'MM/DD/YYYY');

-----------------------------------

Used in SQL

TableName:
SQL SELECT *
FROM testData
$(vIncrementalExpression)

----------------------------------

This translates to this in SQL

SQL SELECT *
From testData WHERE dataDate >= 01/01/2011

NOTICE the missing single quotes around the date. This needs to be

SQL SELECT *
From testData WHERE dataDate >= '01/01/2011'

Thank you for your help

Re: Passing single quotes to a variable - used in SQL where statement

Sokkorn — Tue, 07 May 2013 04:57:50 GMT

Hi darrin,

Maybe this one can do your job. Let try

LET vIncrementalExpression = 'WHERE dataDate >=' & Chr(39) & date($(vStartDate), 'MM/DD/YYYY') & Chr(39);

Regards,

Sokkorn

Anonymous — Tue, 07 May 2013 05:29:39 GMT

Sokkorn,

Thank you very much. I can't believe I didn't think of that and thought it would be something very simple.

Thanks for the quick response.