CODEWITHSUNDEEP
sql-serverazureutcdst
harrisonthu
harrisonthu

Reputation: 454

Convert UTC on Azure to EST Time (including Daylight saving Time-dynamic version)

I have a question about converting UTC time zone to EST time zone since I am still new to SQL language and Azure platform. In log file, I used system built-in function "GetDate()" in log file to get the Date/Time. However, while accessing Azure database on my SSMS, using system-built in function (GetDate()) gives me the datetime in UTC time zone, which is 4 hours ahead of Eastern Time Zone (EST). I have asked similar question before for converting UTC to EST here , and @DanGuzman helped me fix my code. But this question is more about converting UTC to EST (considering Daylight saving time dynamically). Below is my code so far, and I used this link as a reference. However, I would like to make my code dynamic so that I can keep using it for 2020, 2021 as well.

Below code works ONLY for 2019 (since Daylight Saving starts on March 10,2019 until November 3, 2019. Within the date range, below code forward one hour of EST time during daylight saving time range. 

CREATE FUNCTION [dbo].[EST_GetDateTime] 
    (
     -- no parameter
     )
RETURNS datetime
AS
BEGIN

 DECLARE
    @EST datetime,  
    @StandardOffset int,

    @DST datetime,  -- Daylight Saving Time
    @SSM datetime, -- Second Sunday in March
    @FSN datetime  -- First Sunday in November

-- get DST Range
set @EST = CAST(DATEADD(hh,-5,GETDATE()) AS DATETIME)

set @StandardOffset = 0

set @SSM = datename(year,@EST) + '0310'  -- Set which day daylight saving start (for 2019, March 10)
set @SSM = dateadd(hour,2,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
set @FSN = datename(year,@EST) + '1103'   -- Set which day daylight saving start (for 2019, March 10)
set @FSN = dateadd(second,-1,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))

-- add an hour to @StandardOffset if @EST is in DST range
if @EST between @SSM and @FSN
    set @StandardOffset = @StandardOffset + 1

-- convert to DST
set @EST = CAST(DATEADD(hh,-5+@StandardOffset ,GETDATE()) AS DATETIME)

    RETURN @EST
END
GO

Can someone please give any suggestions how to improve my existing code (able to work dynamically) so that I do not need to change the function every single year for adjusting day light saving.

Upvotes: 0

Views: 4639

Answers (3)

Troy Witthoeft
Troy Witthoeft

Reputation: 2676

This should do exactly what you need.

SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE (SELECT CURRENT_TIMEZONE_ID()) AT TIME ZONE 'Eastern Standard Time')

Explanation: This gets the current datetime in the server's local timezone using CURRENT_TIMEZONE_ID(). We then use AT TIME ZONE to make it a datetimeoffset, then we cut that datetimeoffset over to requested timezone... here 'Eastern Standard Time'. Lastly the whole thing is wrapped in a CONVERT() to cut the datetimeoffset objects over to a proper datetime datatype.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46241

One method is a function that converts SYSDATETIMEOFFSET() to EST and converts the result to datetime:

CREATE FUNCTION dbo.EST_GetDateTime()
RETURNS datetime
AS
BEGIN
RETURN(SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime)); 
END
GO

--example usage
SELECT dbo.EST_GetDateTime();
GO

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32737

Since Azure SQL is ahead of the on-premises version, I think you can use the syntax select getutcdate() at time zone 'UTC' at time zone 'Eastern Standard Time'. Also, I'd switch to getutcdate() as that should be invariant regardless of the server's TZ. More information on at time zone here.

Upvotes: 2

Related Questions