'How to convert EST time zone to PST, IST, HST in SQL Server

As the daylight time zone changes so how would I get the correct time zone offset to get the time zone converted.

Ex time difference between EST and IST is 9:30hrs but in daylight time zone(DST) it becomes 10:30 hrs.

So how would I get the correct updated timezone offset

sqlsql-servertimezone-offsetreactjs


Solution 1:[1]

There are a number of different ways to achieve this, and you can use the AT TIME ZONE function to convert datetime values. The below example uses a CTE (CTEDateTimes) as the input for a list of UTC datetime (UTDDateTime) values for the datetimes between midnight and 04:00 22-03-13.

The following statement will return both the UTC datetime and the converted Eastern Standard Time (US).

SELECT
UTDDateTime
,CONVERT(DATETIME2(0), UTDDateTime, 126) AT TIME ZONE 'Eastern Standard Time' EST
FROM CTEDateTimes

The output for around 02:00 hours UTC are as follows:

UTCDateTime             <---> EST
----------------------- ----- ---------------------------
2022-03-13 01:57:00.000 <---> 2022-03-13 01:57:00 -05:00 
2022-03-13 01:58:00.000 <---> 2022-03-13 01:58:00 -05:00
2022-03-13 01:59:00.000 <---> 2022-03-13 01:59:00 -05:00
2022-03-13 02:00:00.000 <---> 2022-03-13 03:00:00 -04:00
2022-03-13 02:01:00.000 <---> 2022-03-13 03:01:00 -04:00
2022-03-13 02:02:00.000 <---> 2022-03-13 03:02:00 -04:00

In this way you can convert to any time zone and then find the offset.

Hope this assists.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LogicalMan

Related Questions