Tuesday, June 2, 2009

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

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.