Post reply

How to convert this to datetime format?

  • June 4, 2025 at 4:19 am

    Go to Answer

    #4602289

    hi everyone

    I have a CSV that is storing date as "20180919T201400+0000".  I am using BULK INSERT to import the file.  How do I convert varchar "20180919T201400+0000" to a datetime format that SS can understand?

    My attempt:

    CONVERT(VARCHAR(25),REPLACE(ANNOUNCE_DATETIME,'"',''),126) AS ANNOUNCE_DATETIME

    Error:

    Conversion failed when converting date and/or time from character string.

    Thank you

    • This topic was modified 3 months ago by water490.

  • June 4, 2025 at 5:54 am

    Answer

    #4602326

    You need to first cast the string to a format that is recognisable to SQL

    Something like this

    DECLARE @SourceTable table (ANNOUNCE_DATETIME varchar(50));

    INSERT INTO @SourceTable (ANNOUNCE_DATETIME)
    VALUES ( '"20180919T201400+0000"' )
         , ( '"20181005T012345-0200"' )

    SELECT src.*
         , rawDateTime  = TRY_CONVERT(datetime, dt.rawDateTime)
         , utcDateTime  = TRY_CONVERT(datetime, dt.rawDateTime AT TIME ZONE 'UTC')
         , cptDateTime  = TRY_CONVERT(datetime, dt.rawDateTime AT TIME ZONE 'Central Pacific Standard Time')
    FROM @SourceTable AS src
    CROSS APPLY (SELECT CONVERT(datetimeoffset(0), STUFF(STUFF(STUFF(STUFF(STUFF(REPLACE(src.ANNOUNCE_DATETIME,'"',''), 19, 0, ':'), 14, 0, ':'), 12, 0, ':'), 7, 0, '-'), 5, 0, '-'))
                ) AS dt(rawDateTime)

    How to post data/code on a forum to get the best help.

  • June 4, 2025 at 11:45 pm

    #4602769

    This is great.  It works.  Thank you so much!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply