SQL Server Forums
Profile | Register | 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
 New Topic  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

3704 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
Constraint Violating Yak Guru

USA
409 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  
 New 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