Filter datatable on date range

Help Studio
, ,
1

Hi Friends,

I need to filter a datatable dt, which is having 50 plus columns and there is one column which is document date. I have two variable in which I have “From Date” and “To date”, means I have to get only data from datatable, which fall under the range of from date and to date variable. I am trying through filter data table, but it is not correctly working. Please help me. If I have from date 01-03-2023 and to date is 05-03-2023 then all rows should be returned which is having 01,02,03,04,and 05 date

2

Hi @adishjain

Please give the following query a try:

filteredDt = (From row In dt.AsEnumerable()
              Let documentDate = DateTime.ParseExact(row.Field(Of String)("Document Date"), "dd-MM-yyyy", CultureInfo.InvariantCulture)
              Where documentDate >= fromDate AndAlso documentDate <= toDate
              Select row).CopyToDataTable()

“Document Date” is the column name of the document date column that you are referring to.

Hope this helps,
Best Regards.

2 Likes
3

Hey @adishjain
Filter Data Table activity is working fine for me. You will have to use Filter Data Table activity 2 times.

first activity configuration:

image
image735×479 14.5 KB

second activity configuration:

image
image713×479 14.3 KB

Reference file:
Test.xaml (10.3 KB)

4

you can try this once it will helps you
dt.AsEnumerable.where(function(a) CDate(a(“ColumnName”).ToString)>=CDate(“01-03-2023”) andalso CDate(a(“ColumnName”).tostring)<=CDate(“05-03-2023”)).CopyToDataTable

thanks and regards

1 Like
5

You only need 1 filter activity. Click on the [ + ] icon to right to add another filter value.

However this filter will only work if the target column has a DateTime format. So if it doesn’t work, check the input (or use the dt.select option to convert while filtering)

6

Your columns must be datetime, not string.

1 Like
7

my date is coming in datetime datatype variable

8

You said it’s a datatable. What’s the datatype of the column in the datatable? If you’re using Read Range or one of the Excel activities to read into the datatable then it’s probably a string column.

9

yes Postwick, it is comming in string in datatable, and I have two variable of datetime datatype using which I need to filter the datatable

10

That’s the problem. The columns in the datatable need to be datetime.

Use Add Data Column to add two datetime columns. For Each Row in Datatable with Assigns to convert the string dates into actual datetime values (DateTime.Parse or DateTime.ParseExact) into the datetime columns.

Then use the new columns in the Filter activity.

11

can you please explain with screenshot?

12

@adishjain

The column need not be readily available in DateTime data type to proceed with the execution. You can basically take the instance of the data & parse it during the runtime & get the required output.

Best Regards.

13

Add the datetime columns:

image

image

Then loop through the datatable and update the new columns with datetime values:

image

CurrentRow(“DateTimeOne”) = DateTime.Parse(CurrentRow(“StringDateOne”).ToString)
CurrentRow(“DateTimeTwo”) = DateTime.Parse(CurrentRow(“StringDateTwo”).ToString)

Now you can use DateTimeOne and DateTimeTwo for filtering against your datetime variables.

1 Like
14

Thank you @Whynotrobot pointing out that Add button. I always prefer to use LINQ over filter datatable activity, somehow I overlooked it.

@adishjain as pointed out by others, I would recommend you to use LINQ for your desired result. LINQ is already given in @Shiva_Nikhil answer.

Let us know if you are facing any issue while implementing it.

15

I have dates in datetime type variables and when I trying to execute it, it shows that the datarow is not containing any data error.

16

Hi @adishjain, can you send me the sample data file. So, I can check from my end and provide proper solution.