How to convert all date in same format

Help Studio
, , , , , , ,
1

In datatable

  I have

Date.

12/08/2022 12:00:00AM
12/09/2022. 04:79:00 PM

12/07/2022.

12/06/2022

I NEED TO CHANGE AS
Date.

12/08/2022
12/09/2022.

12/07/2022.

12/06/2022

2

Hi @sruthesanju

Try this:

dt_Output = outputDataTable = (From row In inputDataTable.AsEnumerable()
                   Let originalDate = DateTime.ParseExact(row.Field(Of String)("Date").Replace(".",""), {"MM/dd/yyyy hh:mm:ss tt","MM/dd/yyyy hh:mm:sstt", "MM/dd/yyyy"}, CultureInfo.InvariantCulture)
                   Select row.SetField("Date", originalDate.ToString("MM/dd/yyyy"))).CopyToDataTable()

dt_Output is of DataType System.Data.DataTable

Hope it helps!!

3

@sruthesanju

Assign Activity:
formattedDataTable = (From row In yourDataTable.AsEnumerable()
                      Let originalDate = If(row.Field(Of Object)("Date") IsNot DBNull.Value, DateTime.ParseExact(row.Field(Of String)("Date"), "MM/dd/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"), row.Field(Of Object)("Date"))
                      Select yourDataTable.Clone().Rows.Add(originalDate)).CopyToDataTable()

row.Field(Of String)(“Date”), “MM/dd/yyyy hh:mm:ss tt”,If you have other formats also you mention in this way {“MM/dd/yyyy hh:mm:ss tt”,“dd-MM-yyyy”,“yyyy-MM-dd”} and the expression is same

4

CopyToDateTable is not member of system.dats.enumerableeowcollection(of system.data.datarow)

5

@sruthesanju Use invoke code activity (please select language to be VBNet) and add the InputDT argument with Direction In/Out and Pass the DT you want to transform.

For Each row As DataRow In InputDT.Rows
   	row("DateColumn") = CDate(row("DateColumn")).Tostring("MM/dd/yyyy")
Next
6

Hi @sruthesanju

Try this:

(From row In dt.AsEnumerable()
 Let originalDate = DateTime.ParseExact(row.Field(Of String)("Date").Replace(".", ""), 
                                       {"MM/dd/yyyy hh:mm:ss tt", "MM/dd/yyyy hh:mm:sstt", "MM/dd/yyyy"}, 
                                       System.Globalization.CultureInfo.InvariantCulture, 
                                       System.Globalization.DateTimeStyles.None)
 Let formattedDate = originalDate.ToString("MM/dd/yyyy")
 Select row).CopyToDataTable()

Regards

7

Hi @sruthesanju

Try this

image
image900×596 34 KB

DT.AsEnumerable().ToList().ForEach(Sub(row)
                                       Dim dateString As String = row("Date").ToString()
                                       Dim formats As String() = {"MM/dd/yyyy h:mm:ss tt", "MM/dd/yyyy hh:mm:ss tt","MM/dd/yyyy h:mmtt", "MM/dd/yyyy", "MM/dd/yyyy."}
                                       Dim parsedDate As DateTime
                                       If DateTime.TryParseExact(dateString, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, parsedDate) Then
                                           row("Date") = parsedDate.ToString("MM/dd/yyyy")
                                       End If
                                   End Sub)
8

Getting error as string was not recognised as valid datetime

9

Getting the same format as in original datatable not like this Mm/dd/yyyy

10

Hi @sruthesanju

Try this:

formattedDataTable = (From row In dt.AsEnumerable()
                          Let originalDate = DateTime.ParseExact(row.Field(Of String)("Date").Replace(".", ""), 
                                                                {"MM/dd/yyyy hh:mm:ss tt", "MM/dd/yyyy hh:mm:sstt", "MM/dd/yyyy"}, 
                                                                System.Globalization.CultureInfo.InvariantCulture, 
                                                                System.Globalization.DateTimeStyles.None)
                          Let formattedDate = originalDate.ToString("MM/dd/yyyy")
                          Select dt.Clone().Rows.Add(formattedDate)).CopyToDataTable()

Please make changes to the last line of code

Regards

11

Only date I am getting in datatable as details are not there

12

Hi @sruthesanju

Welcome to the community!

First we need to understand that we want to convert a date into a desired format for which we need to have only the date from your given string. to do so we can use regex to extract the date from the above mentioned.
Regex to extract the date - System.Text.RegularExpressions.Regex.Match(“12/09/2022. 04:79:00 PM”,“(\d+/\d+/\d+)”).ToString

Then you we can use a date conversion method to achieve your needs.
it can be - Cdate(your extracted date)
or - DateTime.ParseExact(“your extracted date”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

Hope this will solve your issue :blush:

Thanks
Biswajeet kumar

13

Hi @sruthesanju

Try this:

formattedDataTable = (From row In dt.AsEnumerable()
                          Let originalDate = DateTime.ParseExact(row.Field(Of String)("Date").Replace(".", ""), 
                                                                {"MM/dd/yyyy hh:mm:ss tt", "MM/dd/yyyy hh:mm:sstt", "MM/dd/yyyy"}, 
                                                                System.Globalization.CultureInfo.InvariantCulture, 
                                                                System.Globalization.DateTimeStyles.None)
                          Let formattedDate = originalDate.ToString("MM/dd/yyyy")
                          Select dt.Clone().Rows.Add(row(Id), formattedDate, row("Value"))).CopyToDataTable()

Hope it helps!!