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' )
Yes, that is correct thou it was at 4:55:05 PM EST time zone (and military time has it at 8:55 PM?) and we need it converted to Arizona time (US Mountain Standard time) and I also need it formatted to what I have in PBI Completed Date...MM/DD/YYYY hh:mm:ms AM/PM
In the UI, it has this time stamp as you see it below in Arizona Time zone but the database has it at Eastern time zone....
Hope this helps?
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