Finding second sunday of March

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 2005 Forums

SQL Server Administration (2005)

Finding second sunday of March

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-03-26 : 14:01:27

I want to find the date of second sunday of March 2007 to March 2015 (For daylight saving rule).Is there any function in SQL Server which will retrun that?
Thanks.
I want following..

declare @year int
select @year = 2007
select fn_findSecondMonday(@year)
_________________________________
03/11/2007 -- second sunday of March

Sanjeev Shrestha
12/17/1971

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-03-26 : 17:14:36

Can one can help me plz?

Sanjeev Shrestha
12/17/1971

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 17:39:40

[code]create function fn_findSecondMonday(@year int)
returns datetime
as
begin
declare @retDate datetime
set @retDate = cast('3/8/' + cast(@year as varchar(4)) as datetime)
set @retDate = case when datepart(dw, @retDate) < 3 then dateadd(dd, 2 - datepart(dw, @retDate), @retDate)
else dateadd(dd, 9 - datepart(dw, @retDate), @retDate) end
return @retDate
end[/code]

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 17:49:34

Just realized, in your example you named the function SecondMonday, so I created a function for second Monday, but you actually want second Sunday, so this would be the second Sunday

create function fn_findSecondSunday(@year int)
returns datetime
as
begin
	declare @retDate datetime
	set @retDate = cast('3/8/' + cast(@year as varchar(4)) as datetime)
	set @retDate = case when datepart(dw, @retDate) = 1 then @retDate
                            else dateadd(dd, 8 - datepart(dw, @retDate), @retDate) end
	return @retDate
end

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-26 : 18:05:59

what about second sunday before the first monday?

www.elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-26 : 20:31:57

All you have to do is find the first Sunday of March and add seven days to that date. To find the first Sunday in March, you need to find the first Sunday on or before March 7th of any year.

This query uses the logic from the Start of Week Function on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307


select
	[First Sunday of March]= 
	dateadd(dd,((datediff(dd,-53684,dateadd(yy,Year-1900,'19000307'))/7)*7),-53684),
	[Second Sunday of March]= 
	dateadd(dd,((datediff(dd,-53684,dateadd(yy,Year-1900,'19000307'))/7)*7)+7,-53684)

from
	-- Function available here:
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
	( select Year = number from f_table_number_range(2007,2015) ) a

Results:


First Sunday of March   Second Sunday of March 
----------------------- -----------------------
2007-03-04 00:00:00.000 2007-03-11 00:00:00.000
2008-03-02 00:00:00.000 2008-03-09 00:00:00.000
2009-03-01 00:00:00.000 2009-03-08 00:00:00.000
2010-03-07 00:00:00.000 2010-03-14 00:00:00.000
2011-03-06 00:00:00.000 2011-03-13 00:00:00.000
2012-03-04 00:00:00.000 2012-03-11 00:00:00.000
2013-03-03 00:00:00.000 2013-03-10 00:00:00.000
2014-03-02 00:00:00.000 2014-03-09 00:00:00.000
2015-03-01 00:00:00.000 2015-03-08 00:00:00.000

(9 row(s) affected)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-26 : 20:47:15

quote:
Originally posted by jezemine

what about second sunday before the first monday?

www.elsasoft.org

I think that would be in February.


select
	[First Monday of March]= 
	dateadd(dd,(datediff(dd,-53690,dateadd(yy,Year-1900,'19000307'))/7)*7,-53690),
	[Second Sunday before first Monday of March]= 
	dateadd(dd,((datediff(dd,-53690,dateadd(yy,Year-1900,'19000307'))/7)*7)-8,-53690)
from
	( select Year = number from f_table_number_range(2007,2015) ) a

Results:


First Monday of March   Second Sunday before first Monday of March 
----------------------- ------------------------------------------ 
2007-03-05 00:00:00.000 2007-02-25 00:00:00.000
2008-03-03 00:00:00.000 2008-02-24 00:00:00.000
2009-03-02 00:00:00.000 2009-02-22 00:00:00.000
2010-03-01 00:00:00.000 2010-02-21 00:00:00.000
2011-03-07 00:00:00.000 2011-02-27 00:00:00.000
2012-03-05 00:00:00.000 2012-02-26 00:00:00.000
2013-03-04 00:00:00.000 2013-02-24 00:00:00.000
2014-03-03 00:00:00.000 2014-02-23 00:00:00.000
2015-03-02 00:00:00.000 2015-02-22 00:00:00.000

(9 row(s) affected)

CODO ERGO SUM

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-03-27 : 09:21:29

Thanks a lot for your help.
Sanjeev

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-27 : 12:12:56

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by jezemine

what about second sunday before the first monday?

www.elsasoft.org

I think that would be in February.

CODO ERGO SUM

hehe. you a quite the literalist!

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources