SQL Server 用于解析的默认区域性

2021-09-10 00:00:00 sql tsql sql-server

当我运行以下语句时:

SELECT CAST ('12/08/1988' AS DATE) AS BIRTHDAY

我得到的是 1988-12-08，而不是 ISO 8601 格式的 1988-08-12.SQL Server 如何决定申​​请解析的格式?我知道最佳做法，我只是对它的用途以及如何更改它感兴趣?

I get the 1988-12-08, not 1988-08-12 in ISO 8601 format. How does SQL Server decide which format to apply for parsing? I know the best practices, I'm just interested in where does it take it and how it can be changed?

推荐答案

第一.. 解析日期的格式基于当前在活动会话中设置的环境语言.您可以使用 SELECT @@LANGUAGE 查看当前语言，并且可以使用 SET LANGUAGE 命令.

First.. Format for parsing dates is based on an environment language currently set in the active session. You can see the current language with SELECT @@LANGUAGE and you can change it using SET LANGUAGE command.

接下来您可以通过使用 设置日期格式.但是，请注意，如果您再次更改语言，它会覆盖格式设置.

Next you can override the format of current language by setting your own date format with SET DATEFORMAT. However, mind that if you change language again, it overrides the format settings.

以下是一些关于不同设置的行为和影响您的 CAST 查询的示例:

Here are few examples on how different settings behave and affect your CAST query:

SET LANGUAGE Italian
SELECT @@LANGUAGE
, CAST ('12/08/1988' AS DATE) AS BIRTHDAY
, DATENAME(month,CAST ('12/08/1988' AS DATE)) AS MonthName; 

SET LANGUAGE English
SELECT @@LANGUAGE
, CAST ('12/08/1988' AS DATE) AS BIRTHDAY
, DATENAME(month,CAST ('12/08/1988' AS DATE)) AS MonthName; 

SET DATEFORMAT DMY
SELECT @@LANGUAGE
, CAST ('12/08/1988' AS DATE) AS BIRTHDAY
, DATENAME(month,CAST ('12/08/1988' AS DATE)) AS MonthName;

每个新查询的默认语言设置都是在登录级别设置的.您可以通过在对象资源管理器中找到 server->Logins->YourLogin->Properties->Default Language 或使用 ALTER LOGIN 命令

Default language setting for each new query is set on login level. You can change it by finding in Object Explorer on server->Logins->YourLogin->Properties->Default Language or with ALTER LOGIN command

此外，服务器上还有一种默认语言，这会影响新创建的登录名的默认选择.

Further, there is also a default language on server, which affects default choice for newly created logins.

您可以在此问题中找到更多相关信息:如何更改 SQL Server 的默认语言?

More about that you can find in this question: How to change default language for SQL Server?

最后，正如其他人所说，您应该使用 使用样式转换、ISO 格式和适当的数据类型.

At the end, like others said, you should avoid confusion by using CONVERT with style, ISO format and appropriate data types.

我的建议:如果您想在临时查询中将字符串转换为日期(如示例中所示)，请始终使用 ISO 格式，无需担心格式，甚至不需要转换:

My tips: If you want to convert string to date in adhoc queries (like in example), always use ISO format and there is no need to worry about format and not even a need to convert:

SELECT * FROM Table WHERE DateColumn = '20170325'

如果要将日期转换为字符串(用于显示)，请使用具有所需样式的 CONVERT:

If you want to convert date to string (for display) use CONVERT with desired style:

SELECT CONVERT(NVARCHAR(30), DateColumn, 104) FROM Table

相关文章