SQL script to deal with Day Light Savings in SQL server

We have a situation wherein the data from one particular table needs to be displayed in different time-zones. We store the history of one particular SSRS report in this table. This report provides the UI to refresh the data of few tables. It executes one particular procedure in the back-end to do so. The same procedure is scheduled to run on weekly basis using SQL agent as well.
To log last refresh time and the place (agent or report) from where it is done, we created a table in which an entry is made as soon as the procedure is called from either (SQL agent or SSRS report)

Displaying data in different timezone is easy until day light savings rule comes into picture. In order to overcome this I wrote a SQL script so that particular date gets converted to expected timezone.

In the below given script I have tried to display the date in following timezones

• Melbourne
• EST
• PST
• CST
• UTC

Copy script from below line till very end... I hope this helps

/*
Day Light Savings Rule : Starts on the second Sunday of March at 2am and ends on the First Sunday of November at 2am.

Logic :

              1 : List all the day's in March of current year
    : List all the day's in November of current Year
              2 : Filter data from 1 to list all the Sunday's from March
    : Filter data from 1 to list all the Sunday's from November
              3 : Fetch date for 2'nd & 1'st Sunday from March and November respectively

Once the Start and End date for the Day-light savings is identified one just need to check for the date whether it falls in the
date range of Day-Light savings.
*/

DECLARE @Sdate VARCHAR(100) = '03-01-' + (SELECT CONVERT(VARCHAR(10),DATEPART(YEAR,GETDATE()))), -- to fetch first day of March
        @Edate VARCHAR(100) = '11-01-' + (SELECT CONVERT(VARCHAR(10),DATEPART(YEAR,GETDATE()))), -- to fetch first day of November
        @StartDate DATETIME,
        @EndDate DATETIME,
        @UTCDate DATETIME

IF (7 <> (SELECT @@DATEFIRST))
BEGIN
       SET DATEFIRST 7 -- To treat Sunday as first day of the week
END
 
 
;WITH StartDayLightSavings AS 
(
SELECT CONVERT(DATETIME,@Sdate) AS Sdate, DATEPART(WEEKDAY,@Sdate) AS DayOfWeek1,
CONVERT(DATETIME,@Edate) AS Edate, DATEPART(WEEKDAY,@Edate) AS DayOfWeek2

UNION ALL

SELECT DATEADD(DAY,1,Sdate),DATEPART(WEEKDAY,DATEADD(DAY,1,Sdate)),
DATEADD(DAY,1,Edate),DATEPART(WEEKDAY,DATEADD(DAY,1,Edate))
FROM StartDayLightSavings
WHERE MONTH(Sdate) < 4 AND MONTH(Edate) < 12
),
NumberStartDate AS
(
    SELECT
            DATEADD(HOUR,2,Sdate) AS Sdate, -- adding 2 hr since daylightsavings starts at 2 AM
            ROW_NUMBER() OVER (PARTITION BY MONTH(SDate) ORDER BY SDate) AS Num1,
            DATEADD(HOUR,2,Edate) AS Edate, -- adding 2 hr since daylightsavings end at 2 AM
            ROW_NUMBER() OVER (PARTITION BY MONTH(Edate) ORDER BY Edate) AS Num2
    FROM
            StartDayLightSavings
    WHERE
            DayOfWeek1 = 1 -- to fetch all the Sunday's from March
            OR DayOfWeek2 = 1
)
SELECT
    @StartDate = s1.Sdate,
    @EndDate = s2.Edate
FROM NumberStartDate s1
INNER JOIN NumberStartDate S2
ON S1.Num1 = S2.Num2 + 1 AND S1.Num1 = 2
  
--SELECT @StartDate,@EndDate
  
SELECT @UTCDate = DATEADD(SECOND,DATEDIFF(SECOND,SYSDATETIME(),SYSUTCDATETIME()),GETDATE()) --replace getdate() by date you want to convert
    -- @UTCDate = SYSUTCDATETIME()
  
IF(@UTCDate BETWEEN @StartDate AND @EndDate)
BEGIN
SELECT
DATEADD(HOUR,12,@UTCDate) AS Melbourne
,DATEADD(HOUR,-4,@UTCDate) AS EST
,DATEADD(HOUR,-7,@UTCDate) AS PST
,DATEADD(HOUR,-5,@UTCDate) AS CST
,@UTCDate AS UTC
END

IF(@UTCDate NOT BETWEEN @StartDate AND @EndDate)
BEGIN
SELECT
DATEADD(HOUR,11,@UTCDate) AS Melbourne
,DATEADD(HOUR,-5,@UTCDate) AS EST
,DATEADD(HOUR,-8,@UTCDate) AS PST
,DATEADD(HOUR,-6,@UTCDate) AS CST
,@UTCDate AS UTC
END