Get days for daylight saving time

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.

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

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

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-29 : 10:28:28

Perfect, thank you Scott.

Luis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources