SQL 服务器日期时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14769547/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SERVER DATETIME FORMAT
提问by Ian
Studying SQL Server there is something I am not sure of:
学习 SQL Server 有一些我不确定的事情：
A datetimefield with the value:
2012-02-26 09:34:00.000
datetime具有以下值的字段：
2012-02-26 09:34:00.000
If I select out of the table using:
如果我使用以下方法从表中选择：
CAST(dob2 AS VARCHAR(12) ) AS d1
It formats it as:
Feb 26 2012
它将其格式化为：
Feb 26 2012
What I am unsure of his how or why SQL Server formats DateTime like that. If you use datetime2it does not - anyone know why?
我不确定他如何或为什么 SQL Server 像这样格式化 DateTime。如果你使用datetime2它不会 - 有谁知道为什么？
采纳答案by Andomar
The default date format depends on the language setting for the database server. You can also change it per session, like:
默认日期格式取决于数据库服务器的语言设置。您还可以按会话更改它，例如：
set language french
select cast(getdate() as varchar(50))
-->
févr 8 2013 9:45AM
回答by SQLGuru
try this:
尝试这个：
select convert(varchar, dob2, 101)
select convert(varchar, dob2, 102)
select convert(varchar, dob2, 103)
select convert(varchar, dob2, 104)
select convert(varchar, dob2, 105)
select convert(varchar, dob2, 106)
select convert(varchar, dob2, 107)
select convert(varchar, dob2, 108)
select convert(varchar, dob2, 109)
select convert(varchar, dob2, 110)
select convert(varchar, dob2, 111)
select convert(varchar, dob2, 112)
select convert(varchar, dob2, 113)
refernces: http://msdn.microsoft.com/en-us/library/ms187928.aspx
参考：http: //msdn.microsoft.com/en-us/library/ms187928.aspx
回答by Kaf
Compatibility SupportsSays that
Under compatibility level 110, the default style for CAST and CONVERToperations on timeand datetime2data types is always 121. If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.
Compatibility Supports说在兼容级别 110 下，CAST and CONVERT操作time和datetime2数据类型的默认样式始终为 121。如果您的查询依赖于旧行为，请使用小于 110 的兼容级别，或在受影响的查询中明确指定 0 样式。
That means by defaultdatetime2isCAST as varcharto121 format. For ex; col1andcol2formats (below) are same (other than the 0s at the end)
这意味着在默认情况下datetime2是CAST as varchar到121 format。对于前; col1和col2格式（如下）相同（末尾的 0 除外）
SELECT CONVERT(varchar, GETDATE(), 121) col1,
CAST(convert(datetime2,GETDATE()) as varchar) col2,
CAST(GETDATE() as varchar) col3
--Results
COL1 | COL2 | COL3
2013-02-08 09:53:56.223 | 2013-02-08 09:53:56.2230000 | Feb 8 2013 9:53AM
FYI, if you use CONVERTinstead of CASTyou can use a third parameter to specify certain formats as listed here on MSDN
仅供参考，如果您使用CONVERT而不是CAST您可以使用第三个参数来指定MSDN上列出的某些格式
回答by ch2o
In MS SQL Server you can do:
在 MS SQL Server 中，您可以执行以下操作：
SET DATEFORMAT ymd
回答by jai
case when isdate(inputdate) = 1
then convert(datetime, cast(inputdate,datetime2), 103)
else
case when isdate(inputdate) = 0
then convert(datetime, cast(inputdate,datetime2), 103)
回答by Hank Freeman
This is my favorite use of 112 and 114
这是我最喜欢使用的 112 和 114
select (convert(varchar, getdate(), 112)+ replace(convert(varchar, getdate(), 114),':','')) as 'Getdate()
112 + 114 or YYYYMMDDHHMMSSMSS'
Result:
结果：
Getdate() 112 + 114 or YYYYMMDDHHMMSSMSS
Getdate() 112 + 114 或 YYYYMMDDHHMMSSMSS
20171016083349100
20171016083349100
