To fetch based on filter by expression component in ETL

Question

I have filter condition based on date. where it needs fetch records between given dates. in filter by expression I gave as below the field is date datetype and format is YYYYMMDD

fieldname >= '20020502' and fieldname <= '20050430'

but records are not passed next component.

Did I gave the condition righty?

Answer 1

I have another method which always works....

Use date_diff function for the same....

Condition will be provided as below :

Date_diff(date1,fieldname).days >0 & & date_diff(date2,fieldname).days<0

Answer 2

Try typecasting the dates.

(date("YYYYMMDD"))fieldname => '20020502' and (date("YYYYMMDD"))fieldname <= '20050430'

Answer 3

The first point you need to check here is what is the data type of the field.

If the field is a date or datetime type then use directly the functions like date_diff() or so.

If the field is of type string or decimal then a typecasting to date or datetime is necessary before you use the functions like date_diff().

Thanks

Arijit

Answer 4

As you mentioned that

field is already in date datatype with YYYYMMDD format

You only have to typecast the right-hand-side of your expression.

 my_date >= (date("YYYYMMDD")) "20150102" && my_date <= (date("YYYYMMDD")) "20150105"

Considered input data and output is:

20150101

20150102  --> with the above condition goes to output

20150103  --> with the above condition goes to output

20150104  --> with the above condition goes to output

20150105  --> with the above condition goes to output

20150106