2

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
sgordon
  • 53
  • 5
  • do you need time and date ? or just date ? – Alvaro Parra Sep 28 '18 at 21:11
  • 1
    No body reads minds. What exactly does "not having any luck" mean? What did you try? Did it generate an error? Undesirable results? Something else? And what are your intended results? – SMor Sep 28 '18 at 22:03
  • Actually, the correct data type to save this kind of data in the database is `datetimeoffset`. – Zohar Peled Sep 28 '18 at 22:07
  • Apologize for the vague post. I was trying to return the date and the time. I've tried using the import tool with date formats provided and it states it's string data, not a date. So, the import is done as a string and then tried using a convert to datetime. "Conversion failed when converting date and/or time from character string". I was able to use substring to get the date only, but was unable to get with the time. What Zohar suggested below is exactly what was needed. Thank you! – sgordon Oct 01 '18 at 16:39

3 Answers3

2

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
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

In case you need only the date import it as a string and execute the following query

select cast(substring('20171207T000131.000-0600',1,8) as date)

i am extracting '20171207' and converting it to date

Alvaro Parra
  • 796
  • 2
  • 8
  • 23
0

Try this:

SELECT CONVERT(DATETIME,
  STUFF(STUFF(STUFF(STUFF(STUFF(STUFF('20171207T000131.000-0600',20,5,''),14,0,':'),12,0,':'),9,1,' '),7,0,'-'),5,0,'-'),120)
ThorPF
  • 53
  • 6