Filter datatable on date range

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

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.


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:

second activity configuration:

Reference file:
Test.xaml (10.3 KB)

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

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 option to convert while filtering)

Your columns must be datetime, not string.

1 Like

my date is coming in datetime datatype variable

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.

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

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.

can you please explain with screenshot?


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.

Add the datetime columns:



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


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

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.

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.

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