Search code examples
sql-servert-sqltimezone-offset

DateTime2 value not getting converted to Datetime using At Time Zone SQL function

I am writing function to use At Time Zone SQL function to convert timestamp to local timestamp based on timezone value.

The source timestamp column has datetime2 datatype and a value=2020-10-29 23:59:59.997000.

When I am trying to convert it to datetime using

CAST(CAST(Timestamp AS DatetimeOffset) AT TIME ZONE 'Pacific Standard Time' AS DATETIME)

I am getting value=2020-10-29 23:59:59.996667 which is incorrect.

What to be added so value is in datetime.

Solution

  • I believe I may have discovered your issue. When you take your DATETIME2, cast it to DATETIME, then back to DATETIME2(6), you get your result:

    Query to Demonstrate Your Issue

    SELECT CAST(CAST('2020-10-29 23:59:59.997' AS DATETIME) AS DATETIME2(6))

--Returns 2020-10-29 23:59:59.996667

    It's hard to ascertain what you need, but it looks like you are trying to grab the local time in the same datatype DATETIME2(6) as your original data. AT TIME ZONE returns DATETIMEOFFSET, so just need to convert back to DATETIME after (assuming your original data is in UTC time). See below query

    Return Local Time without Offset Info

    DECLARE @d2 DATETIME2(6) = '2020-10-29 23:59:59.997000'

SELECT CAST(@d2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS DATETIME)