如何在 SQL-Server 中将日期转换为 ISO 8601？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16434481/
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
How to convert date to ISO 8601 in SQL-Server?
提问by Kliver Max
I have a column with date format 2006-09-08 14:39:41.000.
I want make a view using this column but I need the date to display in ISO 8601: yyyy-MM-ddThh:mm:ss.SSSZ.
How can I convert it?
我有一个日期格式的列2006-09-08 14:39:41.000。
我想使用此列进行查看，但我需要在 ISO 8601: 中显示日期yyyy-MM-ddThh:mm:ss.SSSZ。
我怎样才能转换它？
回答by Semih Yagcioglu
Conversion code for ISO 8601 is 126, you can use something like this:
ISO 8601 的转换代码是 126，您可以使用如下代码：
SELECT CONVERT(VARCHAR, DateColumn, 126) FROM Table
回答by Freelancer
Try the following:
请尝试以下操作：
SELECT CONVERT(char(30), '2006-09-08 14:39:41.000',126)
Hope it helps.
希望能帮助到你。
回答by UdayKiran Pulipati
The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.
以下示例显示当前日期和时间，使用 CAST 将当前日期和时间更改为字符数据类型，然后使用 CONVERT 以 ISO 8901 格式显示日期和时间。
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO
Here is the result set.
这是结果集。
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ------------------------------ ------------------------------
2006-04-18 09:58:04.570 Apr 18 2006 9:58AM 2006-04-18T09:58:04.570
(1 row(s) affected)
The following example is approximately the opposite of the previous example. The example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.
下面的示例与前面的示例大致相反。该示例将日期和时间显示为字符数据，使用 CAST 将字符数据更改为日期时间数据类型，然后使用 CONVERT 将字符数据更改为日期时间数据类型。
SELECT
'2006-04-04T15:50:59.997' AS UnconvertedText,
CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO
Here is the result set.
这是结果集。
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997
(1 row(s) affected)
