Compare dates from a Data Table

Help Studio
, ,
1

Hello All,

I am getting an input date in string format “20240101” and have adatatable which contains a column with date values .

I need to get the rows whose date value is greater than input date which is “20240101” in this example.

Can any one help how can we achieve this? I tried using filter datatable but its not giving appropriate results.

Any suggestion will be appreciated.

2

Hi @HappyCoding

Try this

filteredRows = (From row In dt.AsEnumerable()
                Where DateTime.ParseExact(row("DateColumn").ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture) > DateTime.ParseExact(inputDate.ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)
                Select row).CopyToDataTable()

Regards,

3

@HappyCoding

filteredDataTable=dataTable.AsEnumerable() _
    .Where(Function(row) DateTime.ParseExact(row.Field(Of String)("YourDateColumnName"), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture) > DateTime.ParseExact("20240101", "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)) _
    .CopyToDataTable()

Replace "YourDateColumnName" with the actual name of the date column in your DataTable. This single expression will filter the rows where the date value is greater than the input date "20240101" and return the result as a new DataTable.

4

we assume that the date are within a default format, otherwise analyze it as described below:
:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

Variable: filterDate | DataType: DateTime =

DateTimeParseExact("20240101","yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).Date

(From d In dtData.AsEnumerable()
Let dp = CDate(d("YourDateColumn").ToString().Trim).Date
Where  dp > filterDate
Select r = d).CopyToDataTable()

Empty Result Handling:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

5

Hi @HappyCoding

Please use the attached XAML File

Happy Automation

image
image1138×832 33 KB

FilterDate.xaml (8.8 KB)

6

I am getting this error

Assign: String ‘10/1/2010’ was not recognized as a valid DateTime.

Actually, The format of date in the datatable is “10/1/2010”.

7

@HappyCoding

Try this

filteredRows = (From row In dt.AsEnumerable()
                Where DateTime.ParseExact(row("DateColumn").ToString(), "dd/M/yyyy", System.Globalization.CultureInfo.InvariantCulture) > DateTime.ParseExact(inputDate.ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)
                Select row).CopyToDataTable()

Can you please confirm which is date and month 10/1/2010

8

10th is date here. It is in DD/M/YYYY

9

@HappyCoding

inputDateString="20240101"
inputDate=DateTime.ParseExact(inputDateString, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)
filteredDataTable=dataTable.AsEnumerable() _
    .Where(Function(row) DateTime.ParseExact(row.Field(Of String)("YourDateColumnName"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) > inputDate) _
    .CopyToDataTable()
10

@HappyCoding

It works try this

Regards,

11

I hope I am writing it in correct way.

image
image1535×311 42.7 KB

Because I am getting error as “Assign: The source contains no DataRows.”

But I checked just before using this assign activity and the datatable row count is 8.

12

Also attaching date column for reference.

image

13

@HappyCoding

Have you tried this

filteredRows = (From row In dt.AsEnumerable()
                Where DateTime.ParseExact(row("DateColumn").ToString(), "d/M/yyyy", System.Globalization.CultureInfo.InvariantCulture) > DateTime.ParseExact(inputDate.ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)
                Select row).CopyToDataTable()
14

@HappyCoding

filteredDataTable = If(dataTable.AsEnumerable() _
    .Where(Function(row) DateTime.TryParseExact(row.Field(Of String)("YourDateColumnName"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing) AndAlso
                      DateTime.ParseExact(row.Field(Of String)("YourDateColumnName"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) > inputDate) _
    .Any(),
    dataTable.AsEnumerable() _
        .Where(Function(row) DateTime.ParseExact(row.Field(Of String)("YourDateColumnName"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture) > inputDate) _
        .CopyToDataTable(),
    dataTable.Clone())
15

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