Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shilpan
Partner Ambassador
Partner Ambassador

Date in the Where Clause

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

1 Solution

Accepted Solutions
shilpan
Partner Ambassador
Partner Ambassador
Author

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

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
shilpan
Partner Ambassador
Partner Ambassador
Author

It is in the same format:

2011-09-06T20:36:38.000Z

Please advise.

Thanks,

Shilpan

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

YYYY-MM-DD 00:00:00

-Rob

shilpan
Partner Ambassador
Partner Ambassador
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III

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