topic Re: Date in the Where Clause in QlikView

Date in the Where Clause

shilpan — Fri, 05 Oct 2012 16:51:47 GMT

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

Re: Date in the Where Clause

Gysbert_Wassenaar — Fri, 05 Oct 2012 16:59:37 GMT

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

Re: Date in the Where Clause

shilpan — Fri, 05 Oct 2012 18:24:19 GMT

It is in the same format:

2011-09-06T20:36:38.000Z

Please advise.

Thanks,

Shilpan

Re: Date in the Where Clause

rwunderlich — Fri, 05 Oct 2012 18:28:35 GMT

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

YYYY-MM-DD 00:00:00

-Rob

Re: Date in the Where Clause

shilpan — Fri, 05 Oct 2012 18:37:30 GMT

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

Re: Date in the Where Clause

rwunderlich — Fri, 05 Oct 2012 21:09:18 GMT

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

Re: Date in the Where Clause

mananmehta900 — Wed, 04 Mar 2020 15:10:10 GMT

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