how to find sundays in a given 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to find sundays in a given month

Author

Topic

subbi39
Starting Member

11 Posts

Posted - 2009-05-29 : 10:32:48

Dear Gurus,
I need a small help in finding all sundays in a given month so that i can use that logic in my sp.

thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 11:47:03

select * from f_table_date('20090601', '20090630')
where weekdayname ='sun'

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

raky
Aged Yak Warrior

767 Posts

Posted - 2009-05-29 : 12:26:02

try this

declare @d1 datetime, @d2 datetime
select @d1 = '5/1/2009',@d2= '5/31/2009'
;with dates ( date )
as
(
select @d1
union all
select dateadd(d,1,date)
from dates
where date < @d2
)
select date from dates where datename(dw,date) = 'Sunday'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:53:38

[code]
declare @month int,@year int,@start datetime
select @month = 5,@d2= 2009,@start=dateadd(mm,@month-1,dateadd(yy,@year-1900,0))
;with month_cte( dateval )
as
(
select @start
union all
select dateadd(d,1,dateval)
from month_cte
where dateadd(d,1,dateval) < DATEADD(mm,1,@start)
)
select count(dateval) from month_cte where datename(dw,dateval) = 'Sunday'
option (maxrecursion 0)
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 22:51:10

[code]
try this one too
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '5/1/2009',@d2= '5/31/2009'

SELECT
number, @d1+number
FROM
master..spt_values
WHERE TYPE ='p'
AND DATEDIFF(d,@d1,@d2) >= number
AND DATENAME(w,@d1+number) = 'Sunday'
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 23:17:51

another way


DECLARE	@date	datetime

SELECT	@date	= GETDATE()

SELECT	[1st_sunday],	DATENAME(weekday, [1st_sunday]),
	[sunday]	= DATEADD(DAY, n * 7, [1st_sunday])
FROM
(
	SELECT	[1st_sunday]	= [1st_month] + 8 - DATEPART(weekday, [1st_month])
	FROM
	(
		SELECT	[1st_month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0)
	) d
) d
CROSS JOIN
(
	SELECT	n = 0 UNION ALL
	SELECT	n = 1 UNION ALL
	SELECT	n = 2 UNION ALL
	SELECT	n = 3 UNION ALL
	SELECT	n = 4 
) n
WHERE	DATEDIFF(MONTH, @date, DATEADD(DAY, n * 7, [1st_sunday])) = 0

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources