How to convert date to ISO 8601 in SQL Server?

Question

I have a column with the date format 2006-09-08 14:39:41.000 .
I want to make a presentation using this column, but I need a date to display in ISO 8601: yyyy-MM-ddThh:mm:ss.SSSZ .
How to convert it?

Answer 1

Try the following:

 SELECT CONVERT(char(30), '2006-09-08 14:39:41.000',126) 

Hope this helps.

Answer 2

The conversion code for ISO 8601 is 126, you can use something like this:

 SELECT CONVERT(VARCHAR, DateColumn, 126) FROM Table 

Answer 3

The following example displays the current date and time, uses CAST to change the current date and time for the character data type, and then uses CONVERT to display the date and time in ISO 8901 format.

 SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS nvarchar(30)) AS UsingCast, CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ; GO 

Here is the result.

 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 roughly corresponds to the previous example. The example displays date and time as character data, uses CAST to change character data for the datetime data type, and then uses CONVERT to change character data to the datetime data type.

 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.

  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)