SQL Select Date Against A Variable

rebelfox — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: SQL Select Date Against A Variable

swuehl — Fri, 01 Apr 2016 15:51:12 GMT

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

Re: SQL Select Date Against A Variable

Gysbert_Wassenaar — Fri, 01 Apr 2016 15:52:02 GMT

Try WHERE MyDate >= '$(varSelectDate)'

Or try WHERE MyDate >=$(varMinDate);

topic Re: SQL Select Date Against A Variable in QlikView

SQL Select Date Against A Variable

Re: SQL Select Date Against A Variable

Re: SQL Select Date Against A Variable