SQL Select Date Against A Variable

[rebelfox]

I am having trouble loading data from an SQL server table on a datetime field.

First I tried a hard coded selection against a literal date.  It worked.

See below.

/ ===============================
// Basic Load With hard Coded Date
// ===============================
LOAD
MyField1,
MyField2,

MyDate;
SQL SELECT *
FROM MyTable
WHERE MyDate >= '01/04/2015';

Then I tried it as a variable that is calculated to set the date to 12 months ago.

// ===================================================
// Code Selection To Last 12 Months As Calculated Date
// ===================================================
Let varTodaysDate = Today();

//
// Start Range Date
Let varDate1 = AddMonths(varTodaysDate,-12);
Let varMinDate = Num(varDate1);
Let varSelectDate = Date(varMinDate,'DD/MM/YYYY');

LOAD
MyField1,
MyField2,

MyDate;
SQL SELECT *
FROM MyTable
WHERE MyDate >=$(varSelectDate);

This second version does not work correctly.  It loads way to many records.

Somehow I have a format problem on the variable varSelectDate even though when I check it in debug it has a value of '01/04/2015'.

Any suggestions appreciated.

[swuehl]

I think you should embed the variable expansion in single quotes to prevent a numeric evaluation of your date format as divisions:

WHERE MyDate >= '$(varSelectDate)';

[Gysbert_Wassenaar]

Try WHERE MyDate >= '$(varSelectDate)'

Or try WHERE MyDate >=$(varMinDate);