Extract dates "dd/MM/yyyy hh:mm:ss tt" on a datatable from a csv file and compare 2 dates on which is the latest

Hi. Im trying to extract a datatable from csv that contains dates on EU/AU format “dd/MM/yyyy hh:mm:ss tt” and determine which is the latest record but met an error.

Tried to use below but met an error.

Input: “11/07/2022 7:24:51 PM” > (Changed Date row from excel)

Assigned to a string: DateTime.ParseExact(row("Changed Date“).ToString,“MM/dd/yyyy hh:mm:dd tt”, System.Globalization.CultureInfo.InvariantCulture).toString(“dd/MM/yyyy hh:mm:dd tt”)

Error: “Assign: String was not recognized as a valid DateTime.”,

Note: Below works when “hh:mm:ss tt” is not included but this is crucial on my part since i have to determine the latest record. Also, i don’t necessarily need to convert to MM/dd/yyyy as long as i can determine which is the latest record up to the second.

DateTime.ParseExact(row("columnname “).ToString.Substring(0, 10),“MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).toString(“dd.MM.yyyy”)

Hi,

Can you replace hh:mm:dd tt with h:mm:ss tt and see if that behaves more like you’d expect.

This works for me using your example input string.

Cheers

2 Likes

Hi, thanks for replying and it now works. But just to confirm,

Do I now have a string with “MM/dd/yyyy h:mm:ss tt” format that i can use to compare which date is latest?

Is it also possible to do datediff with “dd/MM/yyyy” format?

Also if I use below, do i now have a date in dd/mm/yyyy format. Since simply cdate with dd/mm/yyyy does not work?

DateTime.ParseExact(row("Changed Date“).ToString,“dd/MM/yyyy h:mm:ss tt”, System.Globalization.CultureInfo.InvariantCulture).toString(“dd/MM/yyyy h:mm:ss tt”)

Well, you have a string, but that doesn’t necessarily make it any easier to compare than before… as in, it’s still just a string, which is what you started with–you’re just displaying it differently.

If you want to compare dates then I would do it using a System.DateTime variable.

For example, convert your input string to a DateTime variable e.g.
newDateVariable = DateTime.ParseExact(yourInputString,"MM/dd/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture).

Now you have a proper date variable that you can perform any number of date related tasks on e.g.
newDateVariable > anotherDateVariable or newDateVariable.AddMonths(1) etc…

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