How to modify / change / update or replace value as date in data table cell using LINQ?

[Adrian_Star (Adrian Starukiewicz)]

Hi,
I need to modify the date values in columns I and J.

In column I = Date1, I want to change the cell from the date format "MM/dd/yyyy HH:mm:ss" to the format "dd-MM-yyyy HH:mm:ss"

In column J = Date2 I want to change the cell from the date format "MM/dd/yyyy HH:mm:ss" to the format "dd-MM-yyyy"

I’d like to make changes without using a loop through the data table. I think the use of LINQ will be appropriate.
Can someone proficient in LINQ help me?

Sample data:

Place	RMS	Agency_Number	Agency_Name	ID	ID_Name	Policy	Type	Date1	Data2
WARSAW	John Doe	A00000001000	ABC	A00000001001	Someone	920010010101	Policy	07/24/2020 21:33:34	07/24/2020 00:00:00
WARSAW	John Doe	A00000001000	ABC	A00000001001	Someone	920010010101	Policy	07/24/2020 21:13:18	07/24/2020 00:00:00

@ppr

[prasath17 (Guru)]

Hi @Adrian_Star - Could you please check this post…

Convert dd/MM/yyyy HH:mm:ss to 'yyyy-MM-dd'T'HH:mm:ss.fff'Z'

@ranjani - Check this below workflow… [image] Build Datatable [image] Invoke Code dtSample.AsEnumerable().ToList().ForEach(Sub(row) row(“DatefffZ”)=DateTime.ParseExact(row(“DatefffZ”).ToString,“yyyyMMddHHmmss.fff”,System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy-MM-ddTHH:mm:ss.fffZ”)) Invoke Code Edit Arguments [image] Output: [image] Hope this helps…

You just have to tweak the date formats based on your requirements…

This uses Invoke code method, so there is no for each loop.

Hope this helps…

[Adrian_Star (Adrian Starukiewicz)]

Hi @prasath17,
thanks for quick response.
How to handle error?

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.  ---> RemoteException wrapping System.ArgumentException: String was not recognized as a valid DateTime.Couldn't store <15-07-2020 14:12:12> in Date1 Column.  Expected type is DateTime.  ---> RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.

I have invoked code:

DT_Missings.AsEnumerable().ToList().ForEach(Sub(row) row("Date1")=DateTime.ParseExact(row("Date1").ToString,"MM/dd/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy HH:mm:ss"))
DT_Missings.AsEnumerable().ToList().ForEach(Sub(row) row("Date2")=DateTime.ParseExact(row("Date2").ToString,"MM/dd/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"))

[prasath17 (Guru)]

Hi @Adrian_Star - Could you please share the sample excel sheet ? I will try something here and share the results…

[Adrian_Star (Adrian Starukiewicz)]

The result comes directly from the SQL query and I’m working on System.DataTable (I don’t save it to a file). Probably the UiPath set the date format to Columns in DataTable?

This is what the data inside System.DataTable looks like:

In raw view of row dates are not a string:

{...,"Polisa", [07/15/2020 14:12:12], [07/15/2020 00:00:00] }

I will write data to the file only after modification.

[ppr (Peter Preuss)]

@Adrian_Star
modify below xaml on the formats / column names and let run an anylisi job finding the non matching format dates:
FilterDates_NonValidDateFormat.xaml (8.0 KB)

[Adrian_Star (Adrian Starukiewicz)]

@ppr
Works!
But Excel change my format in final file from:

to:

I think there may be such a date as well. The question of the date is open.

[ppr (Peter Preuss)]

@Adrian_Star
It can happen that excel will reformat the values, regardless on how it was set within the datatable. But have a look on following activity and change the cell format after write range: