String not recognised as Valid DateTime

Walnut 1 Reputation point
2021-07-05T00:40:12.587+00:00

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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,406 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Rich Matheisen 45,111 Reputation points
    2021-07-05T02:20:18.943+00:00

    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.

    1 person found this answer helpful.

  2. Ian Xue (Shanghai Wicresoft Co., Ltd.) 31,166 Reputation points Microsoft Vendor
    2021-07-05T04:42:11.613+00:00

    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.

    0 comments No comments

  3. Rich Matheisen 45,111 Reputation points
    2021-07-05T14:48:12.85+00:00

    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"  
    
     
    
      
    
    0 comments No comments