How to handle DST and time zone in sql server 2014 and below

Nov 23 2021 6:46 AM

I have a table which holds server details. I need to perform software updates on weekly basis. servers are located at different locations around the world. How to perform DST(Day light savings) and time zone in sql server 2014. We don't require sql server 2016 since we are not upgrading to 2016 and above.Please find the scripts below

Create table ServerScheduleData(
[servername] [varchar](100) NULL,
[weekofmonth] [int] NULL,
[dayofweek] [int] NULL,
[hourofday] [int] NULL,
[timezone] [varchar](100) NULL,
) On [Primary]
Go

Insert ServerScheduleData ([servername],[weekofmonth],[dayofweek],[hourofday],[timezone]) values ('Server1',1,1,10,'Greenwich Standard Time')
Insert ServerScheduleData ([servername],[weekofmonth],[dayofweek],[hourofday],[timezone]) values ('Server2',2,13,9,'Central Standard Time')
Insert ServerScheduleData ([servername],[weekofmonth],[dayofweek],[hourofday],[timezone]) values ('Server3',1,18,6,'India Standard Time')
Insert ServerScheduleData ([servername],[weekofmonth],[dayofweek],[hourofday],[timezone]) values ('Server4',4,21,22,'Pacific Standard Time')
Insert ServerScheduleData ([servername],[weekofmonth],[dayofweek],[hourofday],[timezone]) values ('Server5',2,3,9,'Greenwich Standard Time')
Insert ServerScheduleData ([servername],[weekofmonth],[dayofweek],[hourofday],[timezone]) values ('Server-6',3,4,23,'Eastern Standard Time')


ALTER PROCEDURE [dbo].[spConverttoUTCTime]
(
@FromDate Datetimeoffset,
@ToDate Datetimeoffset
)
AS BEGIN

declare @day INT;
declare @month INT;
declare @year INT;
declare @hour INT;

set @day=DAY(@FromDate)
set @month=MONTH(@FromDate)
set @year=YEAR(@FromDate)
set @hour=DATEPART(hh,@FromDate)

Select * from (
select servername,DATETIMEFROMPARTS ( YEAR( DATETIMEFROMPARTS (@year, @month, @day, @hour, 0, 0 , 0)),
    MONTH ( DATETIMEFROMPARTS (@year, @month, @day, @hour, 0, 0 , 0)),
    ( 1 +
        (
            (    7 + dayofweek -
                DATEPART(
                    WEEKDAY,
                    DATEFROMPARTS(
                        YEAR( DATETIMEFROMPARTS (@year, @month, @day, @hour, 0, 0 , 0)),
                        MONTH( DATETIMEFROMPARTS (@year, @month, @day, @hour, 0, 0 , 0)),
                        1
                    )
                )
            ) % 7
        ) + ( (weekofmonth-1) * 7)
    ) 
    ,
    hourofday, 0, 0, 0)as ServerScheduleTime
from ServerScheduleData) as s
where CONVERT(Datetimeoffset,s.ServerScheduleTime) >= CONVERT(Datetimeoffset,@FromDate) and  CONVERT(Datetimeoffset,s.ServerScheduleTime) <=
CONVERT(Datetimeoffset,@ToDate)
END
GO