How to Calculate the second sunday for every month

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

General SQL Server Forums

New to SQL Server Programming

How to Calculate the second sunday for every month

Author

Topic

lefrancisco1
Starting Member

2 Posts

Posted - 2009-08-26 : 05:08:18

Folks,

I jus want to find out the second sunday in eery month or given month.Can you please Help me out with this.

Ex- August - Second sundy is 9
sEPTEMBER- Second Sunday is 13

lIKEWISE....
cAN YOU PLEASE heLP ME OUT WITH THIS.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-26 : 05:19:49

try this

select dateadd(dd,8-datepart(dw,'2009-09-01'),'2009-09-01')+7

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

lefrancisco1
Starting Member

2 Posts

Posted - 2009-08-26 : 06:24:17

Hi Senthil Actually its returning correctly but we should find by giving the month if suppose in August what is the second sunday

if i need to get the second sunday for july without considering the first day.

quote:
Originally posted by senthil_nagore

try this

select dateadd(dd,8-datepart(dw,'2009-09-01'),'2009-09-01')+7

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-26 : 06:34:54

Hi try this ,

drop table #Calendar

CREATE TABLE #Calendar(Calendar_Date varchar(32),Month_Num smallint)

DECLARE @dtDate datetime
SET @dtDate = '1/1/2009'
WHILE (@dtDate <= '12/31/2009')
BEGIN
INSERT INTO #Calendar
(Calendar_Date,Month_Num)
SELECT @dtdate,datepart(month,@dtdate)
WHERE datename(dw,@dtdate) = 'Sunday'
SET @dtDate = DATEADD(dd,1,@dtDate)
END

select * from
( SELECT row_number() over ( partition by Month_Num order by Month_Num desc) as rid, * FROM #Calendar)t
where t.rid = 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 06:36:17

See http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx

And the reverse thing here
http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx

N 56°04'39.26"
E 12°55'05.63"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-26 : 06:37:42

[code]
try this
declare @date datetime
select @date = '8/30/2009'

select cast(cast(month(@date)as varchar(2))+'-'+ cast(number as varchar(2))+'-'+ cast(year(@date)as varchar(4))as datetime)
as date into #temp
from master..spt_values AS v
WHERE v.Type = 'P'
AND v.number between 1 and 31

select date from (select date,datename(dw,date)as name,(select count(*) from #temp where date <= t.date
and datename(dw,date) = datename(dw,t.date) group by datename(dw,date) ) cnt
from #temp t) s where cnt = 2 and name = 'sunday'

drop table #temp
[/code]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-26 : 06:38:54

quote:
Originally posted by lefrancisco1

Hi Senthil Actually its returning correctly but we should find by giving the month if suppose in August what is the second sunday

if i need to get the second sunday for july without considering the first day.

quote:
Originally posted by senthil_nagore

try this

select dateadd(dd,8-datepart(dw,'2009-09-01'),'2009-09-01')+7

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

If the month is august you must pass the values as "8" consider remaining as default string "2009-08-01"

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources