| Author |
 Topic  |
|
|
subbi39
Starting Member
India
11 Posts |
 Posted - 05/29/2009 : 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
Sweden
29156 Posts |
Posted - 05/29/2009 : 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 - 05/29/2009 : 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
India
48119 Posts |
Posted - 05/29/2009 : 14:53:38
|
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)
|
|
|
|
bklr
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 05/29/2009 : 22:51:10
|
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'
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 05/29/2009 : 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
Time is always against us
|
|
|
| |
Topic |
|
|
|
how to find sundays in a given month
