SQL不接受PowerShell日期格式 [英] PowerShell date format not accepted in SQL

问题描述

我遇到了一个奇怪的问题.服务器的区域和语言设置设置为英语(美国)时，包含日期和时间的对象没有问题.但是，当我将其更改为我所在国家的本地 Dutch(Belgium)时，我在某些PowerShell脚本中遇到了问题.

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.

PowerShell中是否有某个变量需要设置以解决此问题?还是我必须一直默认使用服务器上的英语日期格式?

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?

例如，我们使用Don Jones提供的 SQL模块来填充SQL数据库.将此行设置为荷兰语时会生成错误，但如果将其设置为英语，则不会生成错误:

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)) }

我唯一要获取日期的方法是获取日期没有什么特别的.并将其设置为 Dutch(Belgium):

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."

为 Excel 工作表生成内容时，我遇到了同样的问题.它还抱怨日期时间格式.

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

推荐答案

要将诸如 DateTime 字段之类的信息导出到SQL，默认语言设置为 English(美国)使用.

For exporting information like DateTime fields to SQL, the default language settings English (United States) are used.

因此，当您的区域和语言设置与SQL的默认设置不同时，它们是:英语(美国)<> 荷兰语(比利时)，则应在代码中使用以下格式以符合英语默认设置:

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:

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

另一种解决方案是更改SQL Express 2014中输入的默认语言:

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

1. 右键单击服务器实例，然后选择 Properties>高级
2. 将默认语言更改为荷兰语(或您使用的语言)
3. 与确定
确认
4. 仍在 SQL Management Studio 中，转到 Security>登录名(在数据库下方)
5. 双击您的用户名，然后选择下面的 Dutch 作为默认语言
6. 重新启动 SQL Management Studio ，您应该会很好

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

结果:在PowerShell代码中使用CmdLet Get-Date 时，将数据传输到SQL时将按设计工作.SQL和Windows的系统语言仍然是英语，但是使用的格式是荷兰语.

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.

这篇关于SQL不接受PowerShell日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！