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 2005 Forums
 Transact-SQL (2005)
 how to find sundays in a given month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

subbi39
Starting Member

India
11 Posts

Posted - 05/29/2009 :  10:32:48  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 05/29/2009 :  11:47:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select * from f_table_date('20090601', '20090630')
where weekdayname ='sun'



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 05/29/2009 :  12:26:02  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/29/2009 :  14:53:38  Show Profile  Reply with Quote

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)
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 05/29/2009 :  22:51:10  Show Profile  Reply with Quote

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'
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/29/2009 :  23:17:51  Show Profile  Reply with Quote
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
Time is always against us

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.09 seconds. Powered By: Snitz Forums 2000