Date in the Where Clause

[shilpan]

Hello,

I'm new to Qlikview. I've been trying to add date range in the where clause, and it is not working. Please advise.

CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;T21;XUserId=BESWWZBNJDbAXQZNOZacCYFMULZYGANOVTbIHZVOTDdcSRdNNTLGGYJOOBcEXSBOSbbIGRD;XPassword=TQaULZFMNLYIWQFGQZMKTZET;";

Let vToday = Date(Today(), 'YYYY-MM-DD');
Let vMonthStart = Date(MonthStart(Today(), 'YYYY-MM-DD'));
Let vMStart = '2012-09-01';
//LOAD *
//FROM Case
//Where ClosedDate > '$(vMonthStart)' AND ClosedDate <= '$(vToday)';

LOAD *;
SQL SELECT *
FROM Case
Where ClosedDate > '$(vMStart)' AND ClosedDate <= '$(vToday)';

This is the runtime error message I get: 

Value for the filter criterion for the field 'ClsoedDate' must be of type Datetime and should not be enclosed in the quotes.

I tried without quotes, and it is not working either.

Thanks,
Shilpan

[Gysbert_Wassenaar]

Make sure your vMStart and vToday variables are in the same date format as ClosedDate is in your database.

[shilpan]

It is in the same format:

2011-09-06T20:36:38.000Z

Please advise.

Thanks,

Shilpan

[rwunderlich]

SFDC is expecting a Datetime, you need to format using Qlikview Timestamp. Should be:

YYYY-MM-DD 00:00:00

-Rob

[shilpan · Accepted solution]

Rob,

Now, it's giving me this error:

unexpected token: '2012-10-05T00:00:00Z'

SQL SELECT *

FROM Case

Where ClosedDate > '' AND ClosedDate <= '2012-10-05 00:00:00'

This is the revised code:

Let vToday = Timestamp(Today(), 'YYYY-MM-DD 00:00:00');
Let vMStart = Timestamp(MonthStart(Today(), 'YYYY-MM-DD 00:00:00'));
LOAD *;
SQL SELECT *
FROM Case
Where ClosedDate > $(vMStart) AND ClosedDate <= $(vToday);

[rwunderlich]

You don't appear to be getting a good value for vMStart. I don't see anything wrong with the example you posted, but verify that in your actual code the spelling and case is exactly the same in your Let and the reference in the SQL.

-Rob

[mananmehta900]

Hello,

It's great that things worked out for you using Timestamp, but if you would still like to use Date function for the where clause, please use like function in the where clause.

EX: 

where FileDate1 like Date(WeekStart(Today()))

FileDate1 is a date. something like "02/02/2020"

Thanks,
Manan Mehta