Powershell String was not recognized as a valid DateTime

  • Hello experts,

    I'm trying to import some CSV data into a SQL database using PowerShell. But when I try to import CSV data that represents date/time info, I get an error, but not with all date/times.

    Does anyone know how I can fix this code so I can import the data without errors? Thanks for any help.

    This code runs successfully:

    $timeinfo = '10/9/2017 11:03:12 AM'
    $template = 'dd/M/yyyy hh:mm:ss tt'
    [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]::InvariantCulture)

    Sunday, September 10, 2017 11:03:12 AM

    But this code throws an error:

    $timeinfo = '4/11/2020 3:05:08 PM'
    $template = 'dd/M/yyyy hh:mm:ss tt'
    [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]::InvariantCulture)
    Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."
    At line:3 char:1
    + [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]: ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FormatException

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • webrunner wrote:

    Hello experts,

    I'm trying to import some CSV data into a SQL database using PowerShell. But when I try to import CSV data that represents date/time info, I get an error, but not with all date/times.

    Does anyone know how I can fix this code so I can import the data without errors? Thanks for any help.

    This code runs successfully:

    $timeinfo = '10/9/2017 11:03:12 AM'
    $template = 'dd/M/yyyy hh:mm:ss tt'
    [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]::InvariantCulture)

    Sunday, September 10, 2017 11:03:12 AM

    But this code throws an error:

    $timeinfo = '4/11/2020 3:05:08 PM'
    $template = 'dd/M/yyyy hh:mm:ss tt'
    [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]::InvariantCulture)
    Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."
    At line:3 char:1
    + [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]: ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FormatException

    -- webrunner

    I think the hint is in dd/M/yyyy, try the latter with dd/mm/yyyy 😉

    😎

  • Thanks! I think it was something else - for some reason the format picks day/month (European?) instead of the US month/day format in the CSV. So the following two examples work now.

    $timeinfo = '10/9/2017 11:03:12 AM'
    $template = 'M/d/yyyy h:mm:ss tt'
    [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]::InvariantCulture)

    $timeinfo = '4/11/2020 3:05:08 PM'
    $template = 'M/d/yyyy h:mm:ss tt'
    [DateTime]::ParseExact($timeinfo, $template, [System.Globalization.CultureInfo]::InvariantCulture)

    Monday, October 9, 2017 11:03:12 AM
    Saturday, April 11, 2020 3:05:08 PM

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply