Post reply

SQL Server datetime. Is there a way to determine the time zone?

  • October 22, 2020 at 6:20 pm

    #3800193

    I have an error Im looking into and I noticed that a datetime is being pulled in the following manner:

    Select CAST(cast(SENT_DT as datetime2) AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard time' as datetime)

    From Table_A

     

    I'm thinking the person who wrote the code was trying to set it to UTC datetime then to CST. But how would sql server know what time zone the original value was? The result is setting the value 5 hours earlier than the original datetime since I'm in CST.

     

  • October 22, 2020 at 7:20 pm

    #3800211

    AT TIME ZONE returns datetimeoffset. Take off the cast and it will show you that it is treating it as UTC.

    "When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone"

    e.g.,

    SELECT 
        SENT_DT AS SENT_DT,
        SENT_DT  AT TIME ZONE 'UTC' AS UtcTimeOffset, 
        CAST(SENT_DT  as datetime2) AT TIME ZONE 'UTC' AS UtcDateTime, 
        SENT_DT  AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard time' AS CdtTimeOffset, 
        CAST(CAST(SENT_DT  as datetime2) AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard time' as datetime) AS CdtDateTime
    FROM TABLE_A;

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

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