Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Get days for daylight saving time

Author  Topic 

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-25 : 10:55:29
Hello all,
is there a way to obtain the last sunday of October and March
of the current year?
This for calculate the daylight saving date.

For example, if I run this query today,
I should get - for October - the day 27 (last sunday).

Thanks in advance.

Luis

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-25 : 11:05:18
quote:
Originally posted by Ciupaz

Hello all,
is there a way to obtain the last sunday of October and March
of the current year?
This for calculate the daylight saving date.

For example, if I run this query today,
I should get - for October - the day 27 (last sunday).

Thanks in advance.

Luis

YOu can calculate those dates - but what I would suggest (and what I have done successfully) is to create a calendar table with the start and end dates.

There are several reasons why a calendar approach is better.
1. You can account for the whims of the congress of the United States. They changed the rules sometime in 2007, I believe.
2. You can take into account multiple countries (Europeans change their time on dates different from the USA and so on).

I used to have a table with all the start and end dates for Europe and USA for a forty year window at my old job. Unfortunately, when that company folded, I forgot to take that with me. I had pulled that info from some website which I can't find at the moment after a quick search.
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-25 : 14:30:34
Ok James, thanks for the advice. I'll try with a table like you.

L
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-28 : 18:20:36
The computation is extremely simple and will be vastly less overhead than a table lookup:



SELECT
DATEADD(DAY, -DATEDIFF(DAY, 6, Mar_End_Month) % 7, Mar_End_Month) AS Mar_Last_Sunday,
DATEADD(DAY, -DATEDIFF(DAY, 6, Oct_End_Month) % 7, Oct_End_Month) AS Oct_Last_Sunday
FROM (
SELECT CAST(YEAR(GETDATE()) AS char(4)) + '0331' AS Mar_End_Month, CAST(YEAR(GETDATE()) AS char(4))+ '1031' AS Oct_End_Month
) AS current_dates

Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-29 : 10:28:28
Perfect, thank you Scott.

Luis
Go to Top of Page
   

- Advertisement -