Tsql Datetime Iso 8601

TSQL DATETIME ISO 8601

When dealing with dates in SQL Server, the ISO-8601 format is probably the best way to go, since it just works regardless of your language and culture settings.

In order to INSERT data into a SQL Server table, you don't need any conversion codes or anything at all - just specify your dates as literal strings

INSERT INTO MyTable(DateColumn) VALUES('20090430 12:34:56.790')

and you're done.

If you need to convert a date column to ISO-8601 format on SELECT, you can use conversion code 126 or 127 (with timezone information) to achieve the ISO format.

SELECT CONVERT(VARCHAR(33), DateColumn, 126) FROM MyTable

should give you:

2009-04-30T12:34:56.790

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

Try the following:

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

Hope it helps.

Import or convert an ISO 8601 date extended with timezone data in TSQL

Well, as I wrote in the comment, SQL Server uses DateTimeOffset to store time-zone aware date and time.

The problem is that you need to translate the no-delimiters ISO 8601 format you use now to the human-readable version of ISO 8601 format - YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm], in order to convert that value into a DateTimeOffset.

You do that by adding the delimiters where they are needed, using STUFF:

Stuff inserts a new string into the existing string, starting in the specified index, instead of the existing substring of the specified length. Since you do not want to remove any existing part of the original string, you use length 0.

I've also added in my demo suggestions of ways to convert that data into date and datetime2 (Not to DateTime, there's a bug with that data type!), in case you do not need accurate information (the time zone alone can easily account for a date change):

DECLARE @DTOString varchar(100) = '20171207T000131.000-0600'

SELECT  CAST(LEFT(@DTOString, 8) As Date) As [Date],

    CAST(
        STUFF(
            STUFF(
                STUFF(
                    LEFT(@DTOString,19)
                , 14, 0, ':')
            , 12, 0, ':')
        ,9, 1, ' ') -- Replacing the T with a space
    As DateTime2) AS [DateTime2], -- NOT to DateTime! there's a bug!      

    CAST(
        STUFF(
            STUFF(
                STUFF(
                    STUFF(
                        STUFF(@DTOString, 23, 0, ':')
                    , 14, 0, ':')
                , 12, 0, ':')
            , 7, 0, '-')
        , 5, 0, '-') 
    As DateTimeOffset) As [DateTimeOffset]

Result:

Date        DateTime2               DateTimeOffset
07.12.2017  07.12.2017 00:01:31     07.12.2017 00:01:31 -06:00

Why is SQL Server misinterpreting this ISO 8601 format date?

It only makes it unambiguous for the newer datatypes (date/datetime2)

For backward compatibility this still is dateformat dependent for datetime.

On SQL Server 2012

SET DATEFORMAT DMY

SELECT CAST('2013-01-05' AS DATETIME),   /*May*/
       CAST('2013-01-05' AS DATETIME2),  /*Jan*/
       CAST('20130105' AS DATETIME),     /*Jan*/
       CAST('20130105' AS DATETIME2)     /*Jan*/

You can use yyyymmdd as an unambiguous format when dealing with those datatypes.

See The ultimate guide to the datetime datatypes (this is referred to as the unseparated format in that article)

---

Related Topics