How to filter datatable based on dates, dynamically?

Hi everyone,

I’m trying to filter datatable based on dates, when I tried with (out_dt=[Date] >= ‘07/15/2019’ and [Date] <= ‘07/31/2019’) it works fine, but I need to get filter data by only giving starting date i.e., 15 and end date, month, year should not be hardcoded.

  1. is it possible to filter data by giving dates dynamically?
    Can anyone help me in this concern?

Hi @farook2

You can use variables to hold the date you want. Then you can use the variable in the Filter Data Table activity to filter based on the values you need without hard coding…

Thanks for your quick response,

But I’m using old uipath version, I don’t have filter datatable activity. Can you please give in select method type?

try with Linq,

create an array of Datarows variable, and use assign with below linq,

arrayOfDataRows = dt.asEnumerable.where(function(x) Date.parseExact(x("dateColumn").toString,"dd-MM-yyyy",Nothing) < DateVariable)

Hi @farook2

Use assign activity where

Newdt=Datatable.Select("[Date]>=‘var1’ and [Date]<=‘var2’"). copytodatatable ()

Thanks
Ashwin.S

Hi
Hope this would help you
—fine we can use select method like this
Yourdatatable = Yourdatatable.Select(“[Datecolumnname] >= ‘07/15/2019’ AND [Datecolumnname] <= # “ + DateTime.DaysInMonth(Now.ToString(“yyyy”),Now.ToString(“MM”)).ToString(“MM/dd/yyyy”) + “ # “ )

Here DateTime.DaysInMonth(Now.ToString(“yyyy”),Now.ToString(“MM”)).ToString(“MM/dd/yyyy”) gives the last day of the month so we don’t need to hard code that

Kindly try this and let know for any queries or clarification
Cheers @farook2

1 Like

Hi @Palaniyappan Thanks for your response,
Instead of giving 07/15/2019, I need to give only starting date i.e.,15. Is there any way to mention only starting date?

Fine Do you mean like this
Yourdatatable = Yourdatatable.Select(“[Datecolumnname] >= ‘07/15/2019’ “)

Cheers @farook2

No, it should be same as your previous method.

But I want to replace starting date 15 instead of 07/15/2019 from above code.

It should look like Yourdatatable = Yourdatatable.Select(“[Datecolumnname] >= ‘15’ AND [Datecolumnname] <= # “ + DateTime.DaysInMonth(Now.ToString(“yyyy”),Now.ToString(“MM”)).ToString(“MM/dd/yyyy”) + “ # “ ).

Can you give me solution for this?
Thanks in advance.

Fine
But On mentioning this I hope it will take value a integer 15 and not date 15
Cheers @farook2

Yeah this is what im worried about, I tried above method again im getting errors,
Can you create a workflow on this? I have attached the sample excel sheet.sample excel.xlsx (8.5 KB)

Uploading: 15643811751402431203339719139425.jpg…

Main.xaml (7.6 KB)
@farook2

Maybe the above code will help

I am using an invoke code activity, so you’r not limited by the studio version or any depencies

A simple code guide:
You are using an assign for the beginDate and endDate to set them as you wish
In the code, I am simply comparing the day to determine the filtering and I’m adding the rows into another table, which in the end will become the inputTable

To elimitate eny errors, if your input date is bigger than the current month max day (ex: endDate = 30 and the month is february) the code will change for that month the endDate to the max days number in the current month from the row, no the system current month

Hope the code will help and good luck to you

The file is showing error, because I’m using old version uipath studio. Can you provide code in select method?

This is the code from the invoke:

Dim dateFromRow As DateTime
Dim lastDayInMonth As Integer
Dim filteredTable As DataTable = table.clone()

If beginDate = 0 Then
beginDate =1
End If

For Each inputRow As DataRow In table.Rows

	Console.WriteLine(inputRow(0).ToString)
	
	dateFromRow = DateTime.ParseExact(inputRow(0).ToString,"MM/dd/yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture)
	lastDayInMonth =DateTime.DaysInMonth(dateFromRow.Year,dateFromRow.Month)
	
	Console.WriteLine(lastDayInMonth.ToString)
	If endDate > lastDayInMonth Then
		endDate = lastDayInMonth
	End If
	Console.WriteLine("Date: "+endDate.ToString)
	
	If (dateFromRow.Day >= beginDate And dateFromRow.Day <= endDate) Then
		filteredTable.ImportRow(inputRow)
	End If

Next
Console.WriteLine(“done filtering”)
table= filteredTable

I’m inserting a picture with the invoke code arguments:
image

As for the first activity that is missing is a multipleAssign
Below are the project local variables:
In the first activity missing is a multiple assign to set the begin and end date

I want to filter my data table according to the current month that is going on.
like if its april i should just filter out april data,if its may then only may data and so on can anyone help.