Convert datatable strings column to datetime

Hi everyone!
i need to convert a column of string(such as “25/08/20”,“15/08/20”,“17/07/20”) from a datatable to datetime formats without loop inside datatable.

i have a problem with this assign:

(From row In Compressed2 let dt= convert.ToDateTime(DateTime.ParseExact(row.Item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)) Select row).CopyToDataTable

nothing happens with this expression… Help Me Please!!

Hey @Angelo_Savino :wave:

Could you please share a sample Excel for reference?

If you want to split the value in a single row then, you could use a temporary array and then use the split() function to split the dates.

If you want to format the respective single date in each column without loop then refer the below link:

In General the LINQ Syntax should/could look like following

(From row In Compressed2.AsEnumerable
Let dt= DateTime.ParseExact(row.Item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)
Select row).CopyToDataTable

  • AsEnumerable is used for bringing the rows into an enumerable sequence
  • There is no need to parseString into datetime (ParseExact Method) and then convert a datetime into a dateTime (Conver.toDate)

But this statement will not do any changes. the Let part stores the converted dateinfo as like a temporary variable, but will not not do any modifications. So the unedited row will be iterated (From In … is same like a for each iteration) and copy it to a datatable.

It looks like you tried to modifiy the datacolumn. This can be done in LINQ with converting and reconstructing the itemArray from row. The modified ItemArray can be used for adding a new row e.g. to an empty cloned datatable.

But updating column with a LINQ within an assign will not work.

Start to do it with a for each, check if the reconstruct the empty datatable approach will work for you. Last resort could be invoke code activity

1 Like

@Angelo_Savino - any reason why you’re againstusing a loop inside the datatable? Using LINQ is still going to iterate through the whole datatable, so why not just make it easy on yourself by doing the following:

Add data column - this will be where you put the new datetime column in your existing datatable. I’ll name this row “NewColumn” but you should name it whatever you want

For each row in Compressed2
Assign row.item(“NewColumn”) = DateTime.ParseExact(row.item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)
next row

Remove Data column - remove the old string column from the existing datatable

Super easy to understand what you’re doing and it can be done in 3-4 activities. It should be extremely fast as well, as it is just doing a single loop through your datatable

1 Like

Hi ,

Here is an activity to convert the data column to datetime in datatable.