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' )
Base on my understanding, there are few key points to resolve your question:
- Convert column data to the datetime type.
- How to use the datetimeoffset type to store datetime and timezone info.
- Use AT TIME ZONE to convert time zones.
- What exactly is the time zone of Arizona.
The converting just like your code:
SELECT DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))
The datetime type doesn't have time zone information, so it's difficult to handle DST conversions. SQL Server provides the datetimeoffset type to store datetime and timezone. You can use AT TIME ZONE to append a time zone to the datetime type and convert it to datetimeoffset type.
AT TIME ZONE function of SQL Server will treat input as target time zone when the input parameter doesn't have time zone info(such as the datetime type). For example, execute
SELECT CAST('2010-02-01 12:00:00' AS DATETIME2 ) AT TIME ZONE 'Eastern Standard Time'
will return
2010-02-01 12:00:00.000 -05:00
When the input contains time zone information, the function converts it into the target time zone using the time zone conversion rules. The target time zone uses the Windows time zone name. The system view sys.time_zone_info contains all the time zones supported by SQL Server. It has a row with a name column has value 'US Mountain Standard Time' and is_current_dst column value is 0, dose that means this time zone can be considered Arizona time? To confirm this, run tzutil /l in the Windows command line, In the list, UTC-07:00 shows that the Arizona time zone name is 'US Mountain Standard Time', which matches the row from sys.time_zone_info，it doesn't use daylight saving time. So what we need to do is call AT TIME ZONE like this:
SELECT CAST('2010-02-01 12:00:00' AS DATETIME2 ) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'US Mountain Standard Time'
Now, full code is here:
SELECT 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 'Eastern Standard Time' AT TIME ZONE 'US Mountain Standard Time'
I made some changes to the code, added a CAST to the value '1970-01-01', converting its type to DATETIME2. This is because the millisecond part of DATETIME type is not precise enough. Adding the number 905 to the millisecond part of DATETIME type, the result will be 907, using DATETIME2 type will be OK.
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?
We are in the Pacific Time Zone now...
It's CRAZY, I know! LOL
In Arizona, you have to use 'US Mountain Standard Time', it need not change time with the season. 'Mountain Standard Time' is used by other State with DST.
Unfortunately, there is no Arizona Time Zone for MS SQL.
Fortunately, the name of Arizona Time Zone in MS SQL is 'US Mountain Standard Time'
Ahhh! I did not know that!!! Thank you for that info!~ Looking at other replies - give me a bit
During Spring and Summer
Arizona Time is 8:49 am
Eastern Time is 11:49 am
During Fall and Winter
Arizona time is 8:49 am
Eastern Time is 10:49 am
Going to check your suggestion and codes now
