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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Get days for daylight saving time
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 10/25/2013 :  10:55:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 10/25/2013 :  11:05:18  Show Profile  Reply with Quote
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

Italy
232 Posts

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

L
Go to Top of Page

ScottPletcher
Aged Yak Warrior

USA
550 Posts

Posted - 10/28/2013 :  18:20:36  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 10/29/2013 :  10:28:28  Show Profile  Reply with Quote
Perfect, thank you Scott.

Luis
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000