Filter Datatable using range date x to y

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 :
image

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

Thankyou

1 Like

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

DateCol date1 BETWEEN date2

Regards,
Karthik Byggari

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.

Hello @KarthikByggari,

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

my variable :

Hello @samir,

i got this error :

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. :slight_smile:

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

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

Example.zip (21.0 KB)

1 Like

Hello @samir,

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

@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.

Update,
using this sequence work :

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

1 Like

Dear @samir,

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

thankyou

@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

1 Like

Hello @samir,

Sorry for asking again, i got an error :
image

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

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.