Filter data based on Date in SQL Script in QS load editor

[pradnya_avrioc]

Hello,

Wish to filter the data from past 4 months from SQL Query and not in LOAD Qlik statement.

SET DateFormat='MM/DD/YYYY';

Let vSetDate = Date(YearStart(Today()),'MM/DD/YYYY'); //01/01/2023

Let vLast4Months = Date(AddMonths(MonthStart(Today()),-4),'MM/DD/YYYY');

[ABC]:
LOAD RowNo() as Srno,
name, perofrmance,
activity_date;

SELECT *
FROM "MW_DATALAKE"."PUBLIC"."ABC" where "activity_date" >= '$(vSetDate)';

Wish to filter the data from past 4 months from SQL Query and not in LOAD Qlik statement.

I have checked the activity_date format - its MM/DD/YYYY only

Trying with or without semicolon and everything but its not filtering the data. Please help.

Do want the syntax to run in Qlik Load but want it in SQL.

[henrikalmen · Accepted solution]

try changing where "activity_date" >= '$(vSetDate)' to this:

where "activity_date" >= cast('$(vSetDate)' as date)

[BrunPierre]

Try this query.

WHERE "activity_date" >= DATEADD(M, -4, GETDATE());

[pradnya_avrioc]

working for vSetdate but not for vLast4Month

[pradnya_avrioc]

Let vSetDate = Date(YearStart(Today()),'MM/DD/YYYY'); //01/01/2023

Let vLast4Months = Date(AddMonths(MonthStart(Today()),-3),'MM/DD/YYYY');  //11/01/2023

Let vMonth = Date(MonthStart(Today(), -4));    //10/01/2023

[henrikalmen]

Maybe you need to specify the date format so that the SQL database is sure of what is month and what is date. See https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ for ideas.

[anat]

SET DateFormat='MM/DD/YYYY';
let vd=date(Today()-75);

LOAD * Where f1>='$(vd)';
LOAD * Inline [
f1
12/20/2022
10/10/2022

];

EXIT SCRIPT;