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