如何得到某月的第N个星期的星期一及星期日的日期?例如:2009年10月的第一个星期的第一天是2009-10-01 第一个星期的最后一天是2009-10-04
第二个星期的第一天是2009-10-05 第二个星期的最后一天是2009-10-11
第五个星期的第一天是2009-10-26 第五个星期的最后一天是2009010-31虚心请教!
第二个星期的第一天是2009-10-05 第二个星期的最后一天是2009-10-11
第五个星期的第一天是2009-10-26 第五个星期的最后一天是2009010-31虚心请教!
然后得到week..取min和max就可以
(
SELECT DATEADD(day,number,'2009-10-01') AS date,
DATEPART(week,DATEADD(day,number,'2009-10-01')) AS week,
DENSE_RANK() OVER(ORDER BY DATEPART(week,DATEADD(day,number,'2009-10-01'))) AS rowid
FROM master.dbo.spt_values
WHERE type = 'p' AND number <=31
AND MONTH(DATEADD(day,number,'2009-10-01'))=10
)
SELECT rowid,MIN(date) AS week_min,MAX(date) AS week_max FROM Liang GROUP BY rowid/*
rowid week_min week_max
-------------------- ----------------------- -----------------------
1 2009-10-01 00:00:00.000 2009-10-04 00:00:00.000
2 2009-10-05 00:00:00.000 2009-10-11 00:00:00.000
3 2009-10-12 00:00:00.000 2009-10-18 00:00:00.000
4 2009-10-19 00:00:00.000 2009-10-25 00:00:00.000
5 2009-10-26 00:00:00.000 2009-10-31 00:00:00.000(5 行受影响)
*/
set @year=2009
set @Month=10
set @n=2;
with cte as (
select 日期,
星期数=datepart(WEEK,日期+@@DATEFIRST-1)
from(
select DATEADD(DAY,number,cast(rtrim(@year)+'-'+rtrim(@month)+'-'+'01' as datetime)) as 日期
from master..spt_values n
where type='p' and number <=31 and MONTH(DATEADD(day,number,cast(rtrim(@year)+'-'+rtrim(@month)+'-'+'01' as datetime)))=@Month) l)select CONVERT(varchar(10),日期,120) as 日期
from (
select DENSE_RANK () over (order by 星期数) as rn ,* from cte ) k
where rn=@n
/*
日期
----------
2009-10-05
2009-10-06
2009-10-07
2009-10-08
2009-10-09
2009-10-10
2009-10-11*/
declare @d as datetime
select @d = GETDATE()
select
第几周= ROW_NUMBER() over(order by DATEPART(week,dt)),
第一天=min(dt) ,
最后一天=max(dt)
From
(
select dt=DATEADD(Day,number,convert(varchar(8),@d,120)+'01')
from master..spt_values P
where type='p' and number <=31 and
MONTH(DATEADD(day,number,Convert(varchar(8),@d,120)+'01'))=MONTH(GETDATE()) --判断是否10月
and (
DATEPART(WeekDay,DATEADD(day,number,Convert(varchar(8),@d,120)+'01'))=1 --星期1
or DATEPART(WeekDay,DATEADD(day,number,Convert(varchar(8),@d,120)+'01'))=7 --星期7
or DATEADD(day,number,Convert(varchar(8),@d,120)+'01')=Convert(varchar(8),@d,120)+'01'--本月第一天
or DATEADD(day,number,Convert(varchar(8),@d,120)+'01')=Dateadd(day,-1,DateAdd(Month,1,Convert(varchar(8),@d,120)+'01'))--本月最后一天
)
) V
group by DATEPART(week,dt)
set datefirst 7
/*第几周 第一天 最后一天
-------------------- ----------------------- -----------------------
1 2009-10-01 00:00:00.000 2009-10-04 00:00:00.000
2 2009-10-05 00:00:00.000 2009-10-11 00:00:00.000
3 2009-10-12 00:00:00.000 2009-10-18 00:00:00.000
4 2009-10-19 00:00:00.000 2009-10-25 00:00:00.000
5 2009-10-26 00:00:00.000 2009-10-31 00:00:00.000(5 行受影响)
*/