Forum Discussion
Change the timezone for date/time - Arizona
If the number 1704228 in db shows time '1/2/2024 1:55:05 PM' in the UI and the time is Arizona Time, then the number stored in db must be an UTC timestamp. Code below will make the conversion
SELECT FORMAT(DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))
AT TIME ZONE 'UTC' AT TIME ZONE 'US Mountain Standard Time', 'MM/dd/yy hh:mm:ss tt', 'en-us' )
I don't see a way if the information is correct that the epoch INT field is already in Eastern Time.
Because SQL Server's built-in datetime functions (e.g., DATEADD(SECOND, epoch, '1970-01-01')) assume the epoch is in UTC, there is no reliable way to convert this value back into an accurate datetime without knowing whether the original timestamp fell under Eastern Standard Time (UTC-5) or Eastern Daylight Time (UTC-4).
Since the epoch is already in Eastern Time, and we don’t know whether DST was in effect at that specific moment, it is not possible to deterministically convert this to UTC or any other time zone using standard SQL Server logic.
The Eastern time is coming from the Product UI. And when it is updated in the database, it reflects the user input.
Actually, I got that backwards. It reflects Arizona time input but the database has it at Eastern time.
My bad!
