PowerShell date format not accepted in SQL

Question

I'm having kind of a strange problem. When the server's Region and Language settings is set to English (United States) there are no issues with objects containing the date and time. But when I change it to my countries local Dutch (Belgium) I am experiencing problems in some of my PowerShell scripts.

Is there somewhere a variable in PowerShell that needs to be set to fix this? Or do I have to default to English date formats on the server all the time?

We use for example the SQL module provided by Don Jones for filling up an SQL database. This line generates an error when it's set to Dutch, but not when it's set to English:

if ($properties[$property] -eq 'datetime2') { [datetime]$prop = $result.GetDateTime($result.GetOrdinal($property)) }

The only thing I do to retrieve the date is Get-Date without anything special. And it generates this error when set to Dutch (Belgium):

Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when converting date and/or time from char
acter string."

I've experienced the same problem when generating stuff for Excel sheets. It also complains about the date time format.

See this thread once http://stackoverflow.com/questions/2379514/powershell-formatting-values-in-another-culture — Rahul, Jul 31 '14 at 09:25
Thank you Rahul, I knew about this workaround. But why isn't it just accepting `[datetime]` in the same way that `Get-Date` is used? It should normally work without adding extra functions, no? — DarkLite1, Jul 31 '14 at 09:37
AFAIK, the default has to to be `en-us` format. are you just trying to format the result of `get-date`? — Rahul, Jul 31 '14 at 09:38
I've found a quick workaround,without having to change my `Region and Language` settings by using `Get-Date -Format "yyyyMMdd HH:mm:ss"` instead of just `Get-Date`. Then it works for the SQL module. I guess it just expects to receive this format and the Belgian format is different and for that reason will not be accepted. — DarkLite1, Jul 31 '14 at 09:43
That's what I was exactly talking about and that's why asked you whether are you just trying to format the result of get-date in above comment. Good job :) — Rahul, Jul 31 '14 at 09:44
Got it now, but didn't get it at first (if that is a sentence at all :P). This solution of pre-formatting works out just fine for me. Thank you Rahul :) — DarkLite1, Jul 31 '14 at 09:48
You may like to post the solution as answer, if in case it helps future readers. — Rahul, Jul 31 '14 at 09:50

DarkLite1 · Accepted Answer · 2023-02-01T13:17:46.237

For exporting information like DateTime fields to SQL, the default language setting is English (United States).

So when your Region and Language settings are different from the default for SQL, being: English (United States) <> Dutch (Belgium), you should use the following format in your code to comply to the English defaults:

Get-Date -Format 'yyyy-MM-dd HH:mm:ss'
$_.whenCreated.ToString('yyyy-MM-dd HH:mm:ss')

Another solution is to change the default language for input in SQL Express 2014:

Right click the server instance and select Properties > Advanced
Change Default language to Dutch (or what you use)
Confirm with Ok
Still in SQL Management Studio go to Security > Logins (below Databases)
Double click your user name and select Dutch below as Default language
Restart SQL Management Studio and you should be good to go

Result: When you use the CmdLet Get-Date in your PowerShell code it will work as designed when transferring data to SQL. The system language for SQL and Windows is still English but the formatting used is Dutch.

Just confirmed this, I noticed it wasn't marked as an answer so I just wanted to post that this does work and resolved my issue. — Lord Helmet, Oct 27 '16 at 19:22

Jakobii · Answer 2 · 2018-03-12T10:05:40.573

I use this for Datetime2 in SQL Server.

Function Get-Datetime2([datetime]$Date = $(Get-Date) ) {
    $DT = Get-Date -Date $Date
    [string]$DateTime2 =  $DT.Month.ToString() + '/'
    [string]$DateTime2 += $DT.Day.ToString() + '/'
    [string]$DateTime2 += $DT.Year.ToString() + ' '
    [string]$DateTime2 += $DT.TimeOfDay.ToString()
    return $DateTime2
}

Get-Datetime2

returns something that looks like this.

3/12/2018 03:04:34.7496659

score 0 · Answer 3 · edited Mar 17 '19 at 01:06

If you already use Don Jones' module you may already be familiar with type-extension through the *.types.ps1xml... There should be one in his module too, if I remember correctly.

When adding the following type declaration,

  <Type>
    <Name>System.DateTime</Name>
    <Members>
      <ScriptMethod>
        <Name>ToString</Name>
        <Script>
          $(Get-Date $This -Format "yyyy-MM-dd HH:mm:ss")
        </Script>
      </ScriptMethod>
    </Members>
  </Type>

You basically override System.DateTime's ToString() method to the format needed by a standard SQL Server installation.

This way you can make sure that every time you load the SQL module dates they are being formatted the right way.

PowerShell date format not accepted in SQL

3 Answers3

Linked

Related