Filter Datatable using range date x to y

[aliaga (Ali Aga Mustofa)]

Hello,

I have some problem filtering datatable using filter sequence. so i have datatable from excel, i have to filter it by range date from ‘01/08/2019’ to ‘20/11/2019’.
Column Filtered :

is filter datatable sequence solve all my problem? or other sequence/code?

Thankyou

[KarthikByggari]

Filter data table activity will work.
Please use between in the filter condition.

DateCol date1 BETWEEN date2

Regards,
Karthik Byggari

[samir (Samir Mohite)]

Hi @aliaga,

I’ll suggest you to use following code in Assign (After Read range)

Dt1 = Dt1.Select("Letter Date >= #08/01/2019# AND Letter Date <= #11/20/2019#").CopyToDataTable

here,
Dt1 is output variable of read range activity.

[aliaga (Ali Aga Mustofa)]

@samir

Select(“Letter Date >= #08/01/2019# AND Letter Date <= #11/20/2019#”).CopyToDataTable

Hello @KarthikByggari,

i already use this but it will return 0 rows of datatable.

my variable :

[aliaga (Ali Aga Mustofa)]

Hello @samir,

i got this error :

[samir (Samir Mohite)]

Hey @aliaga,

Use this (I’d missed &’ ')

dt.Select("[Letter Date] >= '#08/01/2019#' AND [Letter Date] <= '#11/20/2019#'").CopyToDataTable

This is working on my side. check it and let me know.

[KarthikByggari]

It won’t work here, because your start and end dates are strings here.
Change start and end dates to DateTime variables and try again the filter data table activity.

And also try the expression given by @samir
In the expression, if column name has spaces we need to enclose column names in square brackets. [Column Name]

Regards,
Karthik Byggari

[samir (Samir Mohite)]

@aliaga
Use code in my previous post and If you need i’m attaching sample workflow for your reference.

Example.zip (21.0 KB)

[aliaga (Ali Aga Mustofa)]

Hello @samir,

i’ve try use your sequence and it will return this error :

[samir (Samir Mohite)]

@aliaga,

Check the dates in assign filter,

Example.
if the filter dates range is 1st Jan to 10th Jan and there’s no date falling in the specified range.

1st Feb
23rd Feb
30th March

then it is obvious that, you’ll get this type of error, showing Contains no DataRows.

So kindly check dates in filter and make sure you give 'em in MM/dd/yyyy format.

[aliaga (Ali Aga Mustofa)]

Update,
using this sequence work :

Filter Datatable using range date x to y

@aliaga Use code in my previous post and If you need i’m attaching sample workflow for your reference. Example.zip (21.0 KB)

My Mistake is didnt change the column format in excel to date.
Thankyou for @samir and @KarthikByggari

[aliaga (Ali Aga Mustofa)]

Dear @samir,

for Static Date what format/variable type i have to input to change #08/01/2019# ?

thankyou

[samir (Samir Mohite)]

@aliaga
If you’re using variable to give input as date then you have to use String var of with MM/dd/yyyy format. As shown in below shot,

dt.Select("[Letter Date] >= '#"+date1+"#' AND [Letter Date] <= '#"+date2+"#'").CopyToDataTable

e.g

[aliaga (Ali Aga Mustofa)]

Hello @samir,

Sorry for asking again, i got an error :

[samir (Samir Mohite)]

@aliaga

Sorry for asking again,

No worries,

Your Start Date and End Date should be in MM/dd/yyyy format. so change that End Date —> 11/21/2019 (instead of 21/11/2019) @aliaga