Datetime format in datatable

Help Studio
1

Hi all,

I am reading an excel input file, column ‘initial date’ and then I need to filter this date to the previous day.

I use this where clause:

From d in invoicesDT.AsEnumerable
Let dp = Cdate(d(“initial date”).toString).Date
Where dp = now.AddDays(-1).Date
Select r = d).ToList

The problem is as follows

Assign: Conversion from string “31/10/2022” to type Date is not valid

So it would appear that the date read from the input file is in the format ‘dd/MM/yyyy’, so it cannot do the conversion.
How can I solve this?

2

Hi @Kumar802

Can you try with this expression

From d in invoicesDT.AsEnumerable
Let dp = DateTime.ParseExact(d("initial date").toString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")
Where dp = now.AddDays(-1).Date
Select r = d).ToList

Regards
Gokul

3

Updated expression @Kumar802

(From d In Dt1.AsEnumerable
Let dp = DateTime.ParseExact(d("initial date").toString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")
Where dp = DateTime.Now.AddDays(-1).ToString("dd/MM/yyyy")
Select r = d).ToList

Regards
Gokul

4

Thanks for the help.

During execution, i’ve this error message:

Assign: String was not recognized as a valid Datetime

5

Can you share the sample excel file @Kumar802

6

unfortunately I cannot share it, but when I print the datatable via output datatable the result is this:

Initial date
31/10/2022
08/10/2022
21/10/2022
06/11/2022

7

Trim the string value inside the expression. Might have extra spaces before/after some of the values.

Also, you should run it in debug so it stops when it hits the error, then you can look in the debug panels to see the value it’s trying to convert.

8

Can you share the workflow screenshot? @Kumar802

Can you use For each row activity and send us the date format?

Have you tried with this expression

Regards
Gokul

9

Capture3
Capture3918×260 18.4 KB

Here the log message from for each row of datatable, column “initial date”
Capture
Capture2

10

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

1 Like
11

Why are you converting both to strings for comparison? You shouldn’t do that. Compare them as dates.

And the trim is supposed to go on the end of d(“initial date”).ToString because that’s the input to ParseExact that is compared against the format.

And why do the Let and Where? Just do a Where…

Where DateTime.ParseExact(d(“initial date”).ToString.Trim,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture).Date = Now.AddDays(-1).Date

1 Like
12

Tried without Let, same error: String was not recognized as a valid DateTime, i don’t know where is the problem

13

If you’re interested, this would be simple to do with basic activities.

  1. Add Data Column (datetime column)
  2. For Each Row in Datatable
    2.A populate datetime column from “initial date” using ParseExact
    /2 End For Each Row
  3. Filter Datatable

And it would be easier to troubleshoot.

14

The problem is that the string in “initial date” doesn’t fit format “dd/MM/yyyy”

FYI blank values will cause this, the same as improperly formatted values.

15

Thanks Paul, yes, the problem are some hide rows blank and other with some random value (“TOTAL”), how can i remove all the row from the expression except for the Date?

16

This is a good reason why it would be better to do this with activities. Then you could use a Try/Catch to handle if the “initial date” value isn’t parseable.

17

I grasped the point of managing it via activities and the for each row. I always thought that linqs were more efficient than the for each row approach.

18

I have read the guide peter, one thing is not clear to me.

Once you have analysed the excel file, and since the date is read in this format “dd/MM/yyyy” you apply what is written in the guide.

If one day the way the date is read changes (because it changes within the excel file) and it becomes MM/dd/yyyyy, how do you deal with that?

I have seen the approach of using the various date formats within the array. But if for example you have this date 05/04/2022, even handling it via an array, how do you distinguish between 5th of April and 4th of May?

19

This is basically a myth. LINQ still has to loop. And the For Each Row probably has LINQ inside it anyway.

1 Like
20

The expression will use the first format that matches.

So if your formats are {“MM/dd/yyyy”,“dd/MM/yyyy”} you’ll get May 4 2022. If you switch them around to {“dd/MM/yyyy”,“MM/dd/yyyy”} then you’ll get April 5 2022.

1 Like