String not recognised as Valid DateTime

Question

So I have 99% of my code but wondering if someone can help me just get this right please?

This is my code:

Import-csv  $newFile | % {$_.Created = [datetime]::ParseExact($_.Created,'dd/MM/yyyy HH:mm:ss',$null);$_}  |
Export-csv  $newFile2 -Delimiter '~' -NoTypeInformation -Encoding UTF8

Now the error I get is this:
Cannot convert value "13/05/2021 12:13:04" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."

however, when I do this:

Import-csv  $newFile | % {$_.Created = [datetime]::ParseExact('01/01/2021 05:01:56','dd/MM/yyyy hh:mm:ss',$null);$_}  |
Export-csv  $newFile2 -Delimiter '~' -NoTypeInformation -Encoding UTF8;

It works with the hardcoding.
Bonus points if you can help me figure out to get the AM/PM into it as well please

Any thoughts?

The datformat in the sourcefile is dd/mm/yyyy hh:mm:ss which in excel is a custom date format

Answer 1

Am I missing something???

Your example code uses "01/01/2021 05:01:56' as the date/time. The error message you cite has "13/05/2021 12:13:04". But both examples are hardcoded.

It's possible that your problem is a "culture" problem. "13" works if it's properly interpreted as the day, but not if it's interpreted as the month.

However, both examples work . . . at least for me. Verify the culture you're using is appropriate (i.e. verify it with Get-Culture).

For your AM/PM question, it looks like you're using a non-USA format for the date, so is it also the case that you're coding the time in 24-hour format? If that's the case you should capitalize the "hh" in string. The lowercase "hh" interprets the time using a 12-hour format.

Answer 2

Hi,

To get the time along with AM/PM you can use the "tt" format specifier.

$culture = [System.Globalization.CultureInfo]::InvariantCulture  
[datetime]::ParseExact('13/05/2021 12:13:04','dd/MM/yyyy HH:mm:ss',$culture).ToString('dd/MM/yyyy hh:mm:ss tt')  

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I tried your examples and both worked.

$i = @"  
A,Created  
123,13/05/2021 12:13:04  
456,01/01/2021 05:01:56  
"@  
ConvertFrom-CSV -InputObject $i |   
    ForEach-Object {  
        $_.Created = [datetime]::ParseExact($_.Created,'dd/MM/yyyy HH:mm:ss',$null)  
        $_  
    } | Export-csv  c:\junk\newfile.csv -Delimiter '~' -NoTypeInformation -Encoding UTF8  

The resulting CSV looked like this:

"A"~"Created"  
"123"~"5/13/2021 12:13:04 PM"  
"456"~"1/1/2021 5:01:56 AM"  

The Get-Culture cmdlet showed this:

LCID             Name             DisplayName  
----             ----             -----------  
1033             en-US            English (United States)  

Note the use of the month/day/year format of the string representation of the date in the CSV!! If your culture is different you may see different results.

Because you're depending on the culture setting to format the date in the CSV (as it's converted from a DateTime object into a string), you might consider using an explicit coercion to a string rather than depending on the whims of the operating environment. See if this results in the correct results:

$_.Created = ([datetime]::ParseExact($_.Created,'dd/MM/yyyy HH:mm:ss',$null)).ToString("dd/MM/yyyy HH:mm:ss tt")  

That change produces a CSV that looks like this (note the "tt"!):

"A"~"Created"  
"123"~"13/05/2021 12:13:04 PM"  
"456"~"01/01/2021 05:01:56 AM"