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”)


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.



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.