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 am trying to make sense out of your code....and I can't :/
I think the string value of Completed_date is direct from EST. If my understanding is correct, the first 10 digi of the value '1704228905' in your pic is 1704228, use SELECT DATEADD(SECOND, PARSE(LEFT(@ts, 10) AS INT), '1970-01-01') will return '2024-01-02 20:55:05' and this is EST that user inputted.
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!!!!!