Converting UTC to EST with daylight saving

@Jiby Jacob

Thanks for reaching out to Microsoft Q&A.

You're right, the formula doesn't account for Daylight Saving Time (DST). Here are two workarounds to convert UTC to EST considering DST in SQL:

1. Using Zone Identification:

This method uses functions to identify the specific time zone and handle DST adjustments.

SELECT
  toString(
    atTimeZone(fromUTC(currentUTC()), 'America/New_York'),  -- Adjust 'America/New_York' for your specific location
    'yyyyMMddHHmmss'
  ) AS EST_time
FROM your_table;

Explanation:

• atTimeZone(fromUTC(currentUTC()), 'America/New_York'): This function converts the UTC time to the time zone specified by 'America/New_York' (replace with your actual location if needed). It automatically adjusts for DST based on the historical rules for that zone.
• toString(...): This converts the resulting time zone-aware object to a string in the desired format ('yyyyMMddHHmmss').

2. Conditional logic based on DST dates (Less reliable):

This method involves checking if the current date falls within the DST period and applying an offset accordingly. However, it requires maintaining the logic for DST start and end dates, which can be less reliable.

DECLARE @dst_start date = '2024-03-10';  -- Update with actual DST start date for your region
DECLARE @dst_end date = '2024-11-03';  -- Update with actual DST end date for your region
SELECT
  toString(
    CASE
      WHEN current_date >= @dst_start AND current_date < @dst_end THEN
        DATEADD(hour, -4, fromUTC(currentUTC()))  -- Subtracts 4 hours for EDT
      ELSE
        DATEADD(hour, -5, fromUTC(currentUTC()))  -- Subtracts 5 hours for EST
    END,
    'yyyyMMddHHmmss'
  ) AS EST_time
FROM your_table;

Explanation:

• This approach defines variables for DST start and end dates (replace with the actual dates for your region).
• A CASE statement checks if the current date falls within the DST period. If DST applies, it subtracts 4 hours from the UTC time to convert to EDT. Otherwise, it subtracts 5 hours for EST.
  • toString(...) converts the resulting time to the desired format.

Recommendation:

The first method using zone identification is generally preferred as it's more reliable and handles historical DST rule changes automatically. The second method requires manual updates for DST dates and might not be suitable for long-term use.

Hope this helps. Do let us know if you any further queries.