Powershell String was not recognized as a valid DateTime

Question

Post reply

Powershell String was not recognized as a valid DateTime

webrunner

SSC-Dedicated

Points: 31863

June 21, 2021 at 4:23 pm

Go to Answer

#3898900

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

SSC-Dedicated

Points: 31863
More actions
June 21, 2021 at 6:04 pm
Answer
#3898948
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 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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 😉

😎

ricslator Newbie Points: 2 More actions · Answer 2

Parsing a string representation of a c# DateTime is a tricky thing because different cultures have different date formats. .Net is aware of these date formats and pulls them from your current culture (System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat) when you call DateTime.Parse(this.Text); You can either call DateTime.ParseExact and pass in the exact format string that you're expecting, or you can pass in an appropriate culture to DateTime.Parse to parse the date.

For example, this will parse your date correctly:

DateTime.Parse( "22/11/2009", CultureInfo.CreateSpecificCulture("fr-FR") );

Of course, you shouldn't just randomly pick France, but something appropriate to your needs.

What you need to figure out is what System.Threading.Thread.CurrentThread.CurrentCulture is set to, and if/why it differs from what you expect. The IFormatProvider parameter specifies the culture to use to parse the date. Unless your string comes from the user, you should pass CultureInfo.InvariantCulture. If the string does come from the user, you should pass CultureInfo.CurrentCulture, which will use the settings that the user specified in Regional Options in Control Panel.