SQL Server : Query / T-SQL Script to find out Starting and Ending Dates of Daylight Saving for Current Year

2009-06-02T13:45:00.000-07:00

I have worked on lots of complex queries and tasks that invloved extensive programming and logic to be embedded into the SQL Sever T-SQL code.
While for one of the applications in early 2007, that I was working on in India, I had to get the daylight saving dates for that year. But during that time, we did not implement that functionality. While I was working on this problem, I tried to google and find out what could be a possible in-built function in SQL Sever to give me this result. I could not find any such kind of fucntion nor I could not find any one writing about this problem.
I decided to start my own blogs and start writing and helping the community and start learning from my peers, and the first thing that came to my mind was this unsolved problem that I had faced in early 2007. To my surprise, I did not find any solution to this problem and decided that this problem will be my first article to blog.
Please find the Query / T-SQL script below.

DECLARE @CurrentDate AS DATETIME

DECLARE@CurrentDate AS DATETIME

DECLARE@CurrentYear AS INTEGER

DECLARE@FirstDateInMarch AS DATETIME

DECLARE@FirstDateInNovember AS DATETIME

DECLARE@FirstDayInMarch AS VARCHAR(20)

DECLARE@FirstDayinNovember AS VARCHAR(20)

DECLARE@MarchDateAddValue AS INTEGER

DECLARE@NovDateAddValue AS INTEGER

DECLARE@DayLightBeingDate AS DATETIME -- 2nd Sunday of March

DECLARE@DayLightEndDate AS DATETIME -- 1st Sunday of Nov

SELECT@CurrentYear = DATEPART(YY,GETDATE())

--SELECT @CurrentYear = 2010

SELECT @FirstDateInMarch =CONVERT(DATETIME,'03/01/'+CONVERT(VARCHAR(4),@CurrentYear)),

@FirstDateInNovember = CONVERT(DATETIME,'11/01/'+CONVERT(VARCHAR(4),@CurrentYear))

SELECT@FirstDateInMarch,@FirstDateInNovember

SELECT

@FirstDayInMarch = DATENAME(w,@FirstDateInMarch),

@FirstDayInNovember = DATENAME(w,@FirstDateInNovember)

SELECT@FirstDayInMarch,@FirstDayInNovember

SELECT@MarchDateAddValue = CASE WHEN @FirstDayInMarch ='SUNDAY' THEN 14

WHEN @FirstDayInMarch ='MONDAY' THEN 13

WHEN @FirstDayInMarch ='TUESDAY' THEN 12

WHEN @FirstDayInMarch ='WEDNESDAY' THEN11

WHEN @FirstDayInMarch ='THURSDAY' THEN 10

WHEN @FirstDayInMarch ='FRIDAY' THEN 9

WHEN @FirstDayInMarch ='SATURDAY' THEN 8

END,

@NovDateAddValue = CASE

WHEN @FirstDayinNovember ='MONDAY' THEN 6

WHEN @FirstDayinNovember ='TUESDAY' THEN 5

WHEN @FirstDayinNovember ='WEDNESDAY' THEN 4

WHEN @FirstDayinNovember ='THURSDAY' THEN 3

WHEN @FirstDayinNovember ='FRIDAY' THEN 2

WHEN @FirstDayinNovember ='SATURDAY' THEN 1

WHEN @FirstDayinNovember ='SUNDAY' THEN 0

END

SELECT

@MarchDateAddValue,

@NovDateAddValue

SELECT

@DayLightBeingDate = DATEADD(dd,@MarchDateAddValue,@FirstDateInMarch),

@DayLightEndDate = DATEADD(dd,@NovDateAddValue,@FirstDateInNovember)

SELECT@DayLightBeingDate,@DayLightEndDate

SELECT DATENAME(dw,@DayLightBeingDate),DATENAME(dw,@DayLightEndDate)

I hope this post shall be useful to all the user looking out for similar kind of scripts.
Please feel free to leave your comments for the script.

SQL Wishlist

SQL Server : Query / T-SQL Script to find out Starting and Ending Dates of Daylight Saving for Current Year