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.

CodePudding user response：

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)