Convert Date in Datatable

[Dmitri00007 (Dmitri)]

Hi Guys,

I’m reading a datatable and the date format which I’m getting is “dd/MM/yyyy”.
I wanna convert it to “MM/yyyy”

Through Linq or Select Query. because we are dealing with millions of row, can have the liberty to use for each row

Please help

[Gokul001 (Gokul Balaji)]

HI @Dmitri00007

Try this expression

Cdate(Inputstring).Tostring(“MM/yyyy”)

Regards
Gokul

[Gokul001 (Gokul Balaji)]

Alternative Method @Dmitri00007

DateTime.ParseExact(InputString,(“dd.MM.yyyy”),Globalization.CultureInfo.InvariantCulture).ToString(“MM/yyyy”)

dd.MM.yyyy → your Input String Date Format

Regards
Gokul

[Dmitri00007 (Dmitri)]

I wanna convert the date in datatable. the output should be in datatable not string.

Thanks for you effort, Please help

[ppr (Peter Preuss)]

options for updating the column value you will have are presented here:
How to Update Data Column Values of a Data Table | Community Blog

[Gokul001 (Gokul Balaji)]

Hi @Dmitri00007

Try this below expression

dt.AsEnumerable().ToList().ForEach(Sub(row) row(“DatefffZ”)=DateTime.ParseExact(Inputstring/ColumnName,“dd.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/yyyy”))

Have a look on the thread

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

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 cod…

Regards
Gokul

[Dmitri00007 (Dmitri)]

[Gokul001 (Gokul Balaji)]

@Gokul001

Inputstring/ColumnName

You need to declare the Input string

Regards
Gokul

[ashwin.ashok ((TheCodingTheory))]

Hi @Dmitri00007 ,

Assuming that this datatable of yours consists of only a single column:

First clone the datatable like so:
Assign →

dt_result = dt_sampleData.Clone()

Assign →

(From row In dt_sampleData.AsEnumerable()
Let dte = DateTime.ParseExact(row("ColumnWithDate").ToString.Trim,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("MM/yyyy")
Let ra = New Object(){dte}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

Is this what you were looking for?

Kind Regards
Ashwin A.K

[Dmitri00007 (Dmitri)]

Assign: String was not recognized as a valid DateTime.

this is the error, its reflecting

[ashwin.ashok ((TheCodingTheory))]

Hi @Dmitri00007 ,

Would be so kind as to share a sample dataset so that we may be in a better position to assist you?
Dummy data is fine.

Kind Regards,
Ashwin A.K

[Dmitri00007 (Dmitri)]

sample (2).xlsx (54.7 KB)

Date format is dd/MM/yyyy

Expectation - Feb-2022 or Mar-2022

Please help

[ppr (Peter Preuss)]

do stepwise debugging / get paused by breakpoint
use immediate panel and type in: YourDataTableVarName.Rows(0)(ColNameOrIndex)
inspect what is stored within the datatable

Visually referring to Excel is not a guarantee that it is also present in the same format within the datatable

[ashwin.ashok ((TheCodingTheory))]

Hi @Dmitri00007 ,

Is this what you were looking for?

Assign →

str_dateFormat = "MMM-yyyy"

(From row In dt_sampleData.AsEnumerable()
Let dte = DateTime.FromOADate(Convert.ToDouble(row("Date").ToString.Trim)).ToString(str_dateFormat)
Let ra = row.ItemArray.Skip(1).Prepend(dte).ToArray()
Select dt_result.Rows.Add(ra)).CopyToDataTable()

ConvertDate.xaml (6.7 KB)

Kind Regards,
Ashwin A.K