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' )
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' )
BINGO!!!!!!!!
THANK YOU THANK YOU THANK YOU!!!!!
I'm a bit confused, the value in column Completed_date is a 10 digitals number, your code sample use them all( LEFT 10 ) as the seconds since '1970-01-01', why does the code cut its RIGHT 3 digi again as milliseconds? The most right 3 digis are used twice - seconds and milliseconds!
I am not sure why but when I did the Epoch converter check, it is correct. And the format in the code you provided is matching up to our UI date/time format. I used the exact code you provided me.
Our UI format
The Epoch Converter