How to convert date to ISO 8601 in SQL-Server?

Question

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?

score 22 · Answer 1 · answered May 08 '13 at 07:03

22

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

SELECT CONVERT(VARCHAR, DateColumn, 126) FROM Table

answered May 08 '13 at 07:03

Semih Yagcioglu

4,011
1
26
43

The list of format codes can be found here: http://msdn.microsoft.com/en-ca/library/ms187928.aspx – Pier-Luc Gendreau Jun 11 '14 at 02:28
This is the correct answer. – pim Mar 21 '17 at 13:31

score 13 · Accepted Answer · edited Jun 17 '15 at 18:31

13

Try the following:

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

Hope it helps.

edited Jun 17 '15 at 18:31

Diego Jancic

7,280
7
52
80

answered May 08 '13 at 07:00

Freelancer

9,008
7
42
81

6

BUt why its not add `Z` on string end? – Kliver Max May 08 '13 at 07:01
The UTC time zone is sometimes denoted by the letter Z – UdayKiran Pulipati May 08 '13 at 07:06

score 3 · Answer 3 · answered May 08 '13 at 07:03

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.

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.

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)

How to convert date to ISO 8601 in SQL-Server?

3 Answers3

Linked