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

Question

I need to import a flat file into an SQL Server table and it has timestamp data that is in this format:

20171207T000131.000-0600

I have imported as a string, tried to convert, but I am not having any luck.

Answer 1

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