Date in Between Clause not working

[Lesor]

Hi,

I'm trying get a specific data based on date range and I was able to retrieve records using the criteria "CreateDate >= vStartDate", but once I changed to "CreateDate >= vStartDate AND CreateDate <= vStartDate" or "CreateDate BETWEEN vStartDate AND vEndDate" the results is 0.

I've tried formatting the dates via Qlik and SQL and also adding time but it's still not working.

I have the following codes below.

LET vStartDate ='02/04/2023';
LET vEndDate = '02/04/2023';

Load

Field1,

Field2,

CreateDate;

SQL

DECLARE @RUNDATE DATE, @ENDDATE DATE

SET @RUNDATE = CONVERT (datetime,'$(vStartDate )',101)
SET @ENDDATE = CONVERT (datetime,'$(vEndDate)',101)

SELECT Field1, Field2, CreateDate FROM Table1 WHERE CreateDate  @RUNDATE AND @ENDDATE;

[marcus_sommer]

Your condition is simplified: date = X and date = Y which could never be true. You need to change it to something:

date => X and date <= Y

[Lesor]

Hi,

Thank you for the response, already tried this criteria and still 0 result.

[marcus_sommer]

This means that your converting didn't work properly - in general respectively in regard to the data-base specific syntax and/or required data-types. Handling with formatting and data-types is sometimes tricky and tedious and therefore it's best practice to avoid all potential issues by using only pure numbers for all kinds of matching and calculations. So you may reverse your approach. By this occasion you should also have a look on the number behind a date/timestamp because there are different start-values which means you may need to add an appropriate offset-value to harmonize them.

[Lesor]

I would reaaally love if the data is clean as mentioned, problem is I cannot change what the current setup of the database is. ☹️

[marcus_sommer]

At first I would load the data without such where-clause to see how they are looking in Qlik. Especially if the data-set isn't too big you could apply all kinds of converting + formatting and the wanted filtering within Qlik, maybe like:

load * where NewDate >= '$(var1)' and NewDate <= '$(var2)';
load *, date(date#(date, 'FormatPattern')) as NewDate;
sql select F1, F2 date from Source;