Convert String to Datetime and sort a datatable

Help Activities
, , ,
1

I have a datatable with column ‘Date’ in a string format. I need to convert that column into datetime or date format and sort the values in descending order. Once they are sorted, I need to get the difference in dates of the top 2 values. Please help me with the solution.

1 Like
2

can you provide the sample input and expected output

@Tanmay_V_Chetule

3

image
This will be the input datable, Actually it is read from excel so cant convert the datatype, date column will be string only.

Then we need to sort it in descending order acc to date and get the difference between top two dates… So here top two dates will be - 10/27/2023 and 5/19/2023. and then get the difference between them.

4

Add Data Column to create the datetime column.

For Each Row in Data Table, assign newColumnName = DateTime.ParseExact(CurrentRow(“stringDateColumn”).ToString,“format”,System.Globalization.CultureInfo.InvariantCulture)

Sort Data Table on the newColumnName

5

The format I’ve added is MM/DD/YYYY, Sample date - 4/19/2023

The error is string was not recognised as a valid datetime

6

Hi @Tanmay_V_Chetule ,

Could you try changing the format to MM/dd/yyyy and check ?

Or we could try instead of the DateTime.ParseExact method, we could directly try with CDate()

CurrentRow("NewDateColumn") = CDate(CurrentRow("Date").ToString)
7

Use this expression in assign activity

dt = (From r In dt Order By 
If(r(“Date”).ToString.Trim.Length.Equals(10), 
DateTime.ParseExact(r(“Date”).ToString.Trim, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture), DateTime.ParseExact(r(“Date”).ToString.Trim, “M/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture)) Descending Select r).CopyToDataTable

Cheers @Tanmay_V_Chetule

8

MM is for month (must be two digits). dd is for day. yyyy is for year.

9

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