問題描述
當我運行以下語句時:
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
這篇關于SQL Server 用于解析的默認區域性的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！