Last 3 months' data in load script

[#1]

How come the data return does not recognize my "where" clause? I was the data to return only the last 3 months' data. Can anyone help me to translate this into the correct statement?

LOAD SELECT * FROM dbo. A

WHERE Month(TIME_INS) >= month(current date) -3 AND Month(TIME_INS) <= month(current date)

[spividori]

Hi.

where

date(TIME_INS)<=MonthEnd(today()) and date(TIME_INS)>=MonthStart(today(),-2) ;

Regards.

[#3]

Doesn't work.... please help!!!!

it said "SQL Error:[IBM][CLI Driver][DB2/LINUXX8664] SQL0440N No authorized routine named "TODAY"/ "MonthStart" and "MonthEnd" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

SQL Scriptline:
SQL State:42884"

[hector]

Hi, the answer provided by Sandro, is what you asked for, but in a "QlikView Environment", so this is valid code in a Load statement, you can't use the functions in a SQL Select... from statement (they are exceptions to this rule)

You need to use the database functions

For example to extract the month from a timestamp:

in Oracle is extract(month from field)
in Sql server is month(field)
in qlikview is month(field)

Maybe for your database works now() function, and find the functions for add days or months to your field

Rgds

[spividori · Accepted solution]

Hi.

If Hector is right.
You could also try the following:

let hDate = date(today()+(day(today())-1));
let dDate = date(MonthStart(today(),-2));

where date(TIME_INS)>=date('$(dDate)') and date(TIME_INS)<=date('$(hDate)');

Regards.

[#6]

that works!! Thanks a lot!!!