Filter Data Table: String was not recognized as a valid DateTime

Help Studio
, ,
1

Objective: i would like to create a filter which date is greater than or equal to previous date + " 06:00:00" and less than or euqal to current date + " 05:59:59"

Data Record:
TempDate

Filter:
FilterWizard

First Value in Filter:
DateTime.ParseExact(Today.AddDays(-1).ToString+" 06:00:00","MM/dd/yy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture)
Second Value in Filter:
DateTime.ParseExact(Today.ToString+" 05:59:59","MM/dd/yy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture)

Error: Filter Data Table: String was not recognized as a valid DateTime.

3

Hi @arthurang

Check the data type of input data read from excel. If it is not in date format covert it into the date format.

4

:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

5

Hi @arthurang ,

Can you share a sample input file, so that we can look into it?

6

@arthurang

It is because today.tostring will include time
In it…so please use like this

DateTime.ParseExact(Today.AddDays(-1).ToString("MM/dd/yy")+" 06:00:00","MM/dd/yy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture)

Hope this helps

Cheers

1 Like
7

Hi @Anil_G thank you for your reply and apology for not getting back to you sooner. now theres no error but im just wondering why there’s no record has been filtered in my data table.

8

@arthurang

Is temp date column is of dateformat? That might be the issue

You can try like this

Dt.AsEnumerable.Where(function(x) Cdate(x("Temp date").ToString)>= DateTime.ParseExact(Today.AddDays(-1).ToString("MM/dd/yy")+" 06:00:00","MM/dd/yy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture) AndAlso Cdate(x("Temp date").ToString)<= DateTime.ParseExact(Today.ToString("MM/dd/yy")+" 05:59:59","MM/dd/yy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable

Here dt is the input datatable

Use this in assign…output will be the filtereddt

Cheers

1 Like
9

i’m not quite sure if made a correct statement in my invoke code. Before i filtered my data by below code,

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“First Date”) = DateTime.ParseExact(row(“First Date”).ToString,“dd/MM/yyyy HH:mm:ss”, Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
row(“Post Date”) = DateTime.ParseExact(row(“Post Date”).ToString,“dd/MM/yyyy HH:mm:ss”, Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
row("Temp Date")= row("Post Date").ToString +" "+DateTime.FromOADate(Convert.ToDouble(row("Time").ToString)).ToString("HH:mm:ss")
End Sub)
10

@arthurang

If you see the values are stored as strings and not dates…so that is the reason the filter datatable is not working…

So please use the above statement provided in an assign activity…this will convert the left side as well to datetime and then compare

Cheers

11

Hi @arthurang

You can use this expression to filter by date:

dt.Select("DateTime.ParseExact([Temp Date], 'MM/dd/yyyy hh:mm:ss', null) = DateTime.ParseExact('" + date + "', 'dd-MM-yyyy hh:mm:ss', null)")

Regards
Gokul

12

thank you so much @Anil_G it works great :slight_smile: One last thing, how can i format the value to temp date as date

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“First Date”) = DateTime.ParseExact(row(“First Date”).ToString,“dd/MM/yyyy HH:mm:ss”, Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
row(“Post Date”) = DateTime.ParseExact(row(“Post Date”).ToString,“dd/MM/yyyy HH:mm:ss”, Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
row("Temp Date")= row("Post Date").ToString +" "+DateTime.FromOADate(Convert.ToDouble(row("Time").ToString)).ToString("HH:mm:ss")
End Sub)
13

@arthurang

First you have to create that column in date format…so either in your build datatable or add data column activity select the datatype as datetime instead of string…

Then you can use cdate(row("Post Date").ToString +" "+DateTime.FromOADate(Convert.ToDouble(row("Time").ToString)).ToString("HH:mm:ss")) which will store the string as date format

Cheers

1 Like
14

noted, again thank you so much

1 Like
15

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