Date filter in the load editor

[pascaldijkshoor]

Hello,

I want to add a date filter for loading data in the load editor. I have tried several where clauses but all didn't work.

The column name is SHIPMENT_DATE and the date format is for example: 1/23/2016 12:00:00 AM

I want to include only the data from 2017 and 2018, could someone help me with this?

Thank you in advance,

Pascal

[Anil_Babu_Samineni]

You can define

Load * From Table Where SHIPMENT_DATE >= 'Your Date Format' and SHIPMENT_DATE <= 'Your Date needed';

[buzzy996]

try this way as well,

WHERE SHIPMENT_DATE >='2017/01/01 00:00:00'; (check ur date data format if u have any issue)

[pascaldijkshoor]

Thanks for the reply, but both solutions do not work.

[tamilarasu]

Hi Pascal,

Try something like,

Data:

Load *

From

Table

Where Year(SHIPMENT_DATE ) >= 2017;

If the above script is not working then SHIPMENT_DATE field should be in text format in your source table. In that case you can try,

Data:

Load *

From

Table

Where Year(Date#(SHIPMENT_DATE,'M/DD/YYYY hh:mm:ss TT'))  >= 2017;

[tamilarasu]

If you looking for dynamic script, you can try

Let vYear = Year(Addyears(Today(),-1));

Data:

Load *

From

Table

Where Year(SHIPMENT_DATE ) >= $(vYear);

[pascaldijkshoor]

I have tried this one, but I get an error that says: "YEAR": invalid identifier

[tamilarasu]

Looks like you are fetching data from Oracle. If thst is the case, you can try,

Data:

SQL SELECT *

From

Table

Where Extract( Year From SHIPMENT_DATE ) >= 2017;

[alis2063]

If your are loading data before preceding load as in Sql Source then use Cast fund as below.

Data:

SQL SELECT *

From

Table

where Cast(SHIPMENT_DATE as date) between '01-Jan-2017' and '05-Jan-2018'

[isingh30]

Why are you doing this in the main script when you can do this in the table properties. What table are you using? Please specify the format in the number tab.

Basic Date Format - date#(date,'DD.MM.YYYY')

Thanks.