如何得到某月的第N个星期的星期一及星期日的日期? 例如:2009年10月的第一个星期的第一天是2009-10-01 第一个星期的最后一天是2009-10-04
第二个星期的第一天是2009-10-05 第二个星期的最后一天是2009-10-11
第五个星期的第一天是2009-10-26 第五个星期的最后一天是2009010-31
虚心请教!
SET DATEFIRST 1;;WITH Liang AS
(
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 行受影响)
*/
以上是2005的代码,提示有两个地方在2000里不兼容。with 及dense_rank 两个函数在2000里不支持。。请哪位大哥帮我转成2000形式的好不好?
第二个星期的第一天是2009-10-05 第二个星期的最后一天是2009-10-11
第五个星期的第一天是2009-10-26 第五个星期的最后一天是2009010-31
虚心请教!
SET DATEFIRST 1;;WITH Liang AS
(
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 行受影响)
*/
以上是2005的代码,提示有两个地方在2000里不兼容。with 及dense_rank 两个函数在2000里不支持。。请哪位大哥帮我转成2000形式的好不好?
解决方案 »
- |zyciis|第四贴:如何锁死一个表中的一条记录,不被其他事务更新和读取呢 上贴大家给出锁页和加字段表示:那么SQL2005有没有好的解决方法
- 问一个让我纠结很久的问题!
- 谁能回答我???(急)
- 中文问题-----求救!!在线等待!!!11
- sql server 2005 中不能输入汉字,为什么,类型是varchar(50)?
- ===如何用Sql语句改变表中的一行为自动增长类型?===
- 请问在SQL语句里边,要进行分支处理的话,有什么方法?
- 如何这样创建数据库
- mssql中数据库里的表能不能导出来,再导回去。请各位高手指点
- 安装MySQL启动服务失败,装了一天了,求大佬
- 提取数据问题
- 求一句SQL
SELECT rowid,MIN(date) AS week_min,MAX(date) AS week_max FROM
(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)T
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 個資料列受到影響)
*/
declare @d as datetime
select @d = GETDATE()
select
第几周=(select count(DATEPART(week,dt))+1 from TB where DATEPART(week,T.dt)<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
SELECT MIN(date) AS week_min,MAX(date) AS week_max FROM
(SELECT DATEADD(day,number,'2009-10-01') AS date,
DATEPART(week,DATEADD(day,number,'2009-10-01')) AS week
FROM master.dbo.spt_values
WHERE type = 'p' AND number <=31 AND MONTH(DATEADD(day,number,'2009-10-01'))=10)T
GROUP BY [WEEK]
/*
week_min week_max
----------------------- -----------------------
2009-10-01 00:00:00.000 2009-10-04 00:00:00.000
2009-10-05 00:00:00.000 2009-10-11 00:00:00.000
2009-10-12 00:00:00.000 2009-10-18 00:00:00.000
2009-10-19 00:00:00.000 2009-10-25 00:00:00.000
2009-10-26 00:00:00.000 2009-10-31 00:00:00.000
*/
set @date='2009-10-01'select datepart(week ,dateadd(day,number,dateadd(month,datediff(month,0,@date),0))),
min(dateadd(day,number,dateadd(month,datediff(month,0,@date),0))),
max(dateadd(day,number,dateadd(month,datediff(month,0,@date),0)))
from master..spt_values
where
type='p' and number>=0
and dateadd(day,number,dateadd(month,datediff(month,0,@date),0))<=
dateadd(day,-1,dateadd(month,datediff(month,0,@date)+1,0))
group by
datepart(week ,dateadd(day,number,dateadd(month,datediff(month,0,@date),0)))
----------- ----------------------- -----------------------
40 2009-10-01 00:00:00.000 2009-10-04 00:00:00.000
41 2009-10-05 00:00:00.000 2009-10-11 00:00:00.000
42 2009-10-12 00:00:00.000 2009-10-18 00:00:00.000
43 2009-10-19 00:00:00.000 2009-10-25 00:00:00.000
44 2009-10-26 00:00:00.000 2009-10-31 00:00:00.000(5 行受影响)
GO
SELECT MIN(date) AS week_min,MAX(date) AS week_max,[WEEK]INTO # FROM
(SELECT DATEADD(day,number,'2009-10-01') AS date,
DATEPART(week,DATEADD(day,number,'2009-10-01')) AS week
FROM master.dbo.spt_values
WHERE type = 'p' AND number <=31 AND MONTH(DATEADD(day,number,'2009-10-01'))=10)T
GROUP BY [WEEK]
SELECT week_min,
week_max,
(select count(*) from # where [week]<=t.[week])[week]
from # t
/*
week_min week_max week
----------------------- ----------------------- -----------
2009-10-01 00:00:00.000 2009-10-04 00:00:00.000 1
2009-10-05 00:00:00.000 2009-10-11 00:00:00.000 2
2009-10-12 00:00:00.000 2009-10-18 00:00:00.000 3
2009-10-19 00:00:00.000 2009-10-25 00:00:00.000 4
2009-10-26 00:00:00.000 2009-10-31 00:00:00.000 5(5 個資料列受到影響)
*/
SET @dt='2009-10-01'
SET DATEFIRST 1;select convert(varchar(10),min(dt),120),
convert(varchar(10),max(dt),120)
from
(select dt=dateadd(dd,number,@dt),flag=datepart(dw,dateadd(dd,number,@dt))%7,g=datepart(wk,dateadd(dd,number,@dt))
FROM master.dbo.spt_values
WHERE type = 'p' AND number <=31 and dateadd(dd,number,@dt)<=dateadd(dd,-1,convert(varchar(7),dateadd(mm,1,@dt),120)+'-01'))t
group by g/*
---------- ----------
2009-10-01 2009-10-04
2009-10-05 2009-10-11
2009-10-12 2009-10-18
2009-10-19 2009-10-25
2009-10-26 2009-10-31(所影响的行数为 5 行)*/
SELECT MIN(date) AS week_min,
MAX(date) AS week_max,
cnt as [week]
FROM
(
SELECT DATEADD(day,number,'2009-10-01') AS date,
DATEPART(week,DATEADD(day,number,'2009-10-01')) AS week,
cnt=(select count(distinct DATEPART(week,DATEADD(day,number,'2009-10-01'))) from master.dbo.spt_values
WHERE type = 'p' AND number <=31 AND MONTH(DATEADD(day,number,'2009-10-01'))=10
and DATEPART(week,DATEADD(day,number,'2009-10-01'))<=DATEPART(week,DATEADD(day,t.number,'2009-10-01')))
FROM master.dbo.spt_values t
WHERE type = 'p' AND number <=31 AND MONTH(DATEADD(day,number,'2009-10-01'))=10
)t
group by [cnt]
/*
week_min week_max week
----------------------- ----------------------- -----------
2009-10-01 00:00:00.000 2009-10-04 00:00:00.000 1
2009-10-05 00:00:00.000 2009-10-11 00:00:00.000 2
2009-10-12 00:00:00.000 2009-10-18 00:00:00.000 3
2009-10-19 00:00:00.000 2009-10-25 00:00:00.000 4
2009-10-26 00:00:00.000 2009-10-31 00:00:00.000 5(5 個資料列受到影響)
*/
SET @dt='2009-10-01'
SET DATEFIRST 1;
select convert(varchar(10),min(dt),120),
convert(varchar(10),max(dt),120)
from
(select dt=dateadd(dd,number,@dt),flag=datepart(wk,dateadd(dd,number,@dt))
FROM master.dbo.spt_values
WHERE type = 'p' AND number <=31 and dateadd(dd,number,@dt)<=dateadd(dd,-1,convert(varchar(7),dateadd(mm,1,@dt),120)+'-01'))t
group by flag/*
---------- ----------
2009-10-01 2009-10-04
2009-10-05 2009-10-11
2009-10-12 2009-10-18
2009-10-19 2009-10-25
2009-10-26 2009-10-31(所影响的行数为 5 行)*/
SET DATEFIRST 1;
select wk,min(d) as mind ,max(d) as maxd
from (
select dateadd(d,number,'2009-10-01')as d,
datepart(wk,dateadd(d,number,'2009-10-01'))-datepart(wk,'2009-10-01')+1 as wk
FROM master.dbo.spt_values
WHERE type = 'p' AND number <=30
) a
group by wk
SET @dt='2009-10-01'
SET DATEFIRST 1;
select '第'+left(flag,2)+ '周',
convert(varchar(10),min(dt),120),
convert(varchar(10),max(dt),120)
from
(select dt=dateadd(dd,number,@dt),flag=datepart(wk,dateadd(dd,number,@dt))-datepart(wk,@dt)+1
FROM master.dbo.spt_values
WHERE type = 'p' and dateadd(dd,number,@dt)<=dateadd(dd,-1,convert(varchar(7),dateadd(mm,1,@dt),120)+'-01'))t
group by flag/*
-------- ---------- ----------
第1周 2009-10-01 2009-10-04
第2周 2009-10-05 2009-10-11
第3周 2009-10-12 2009-10-18
第4周 2009-10-19 2009-10-25
第5周 2009-10-26 2009-10-31(所影响的行数为 5 行)*/
set @year=2009
set @Month=10
set @n=2;select 日期,
星期数=datepart(WEEK,日期+@@DATEFIRST-1) into #
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) p
select 周数,
日期=CONVERT(varchar(10),日期,120) from
(
select
周数=(select COUNT(distinct 星期数)+1 from # where k.星期数>星期数),
日期
from # k) p
where 周数=1
/*
周数 日期
----------- ----------
1 2009-10-01
1 2009-10-02
1 2009-10-03
1 2009-10-04*/
--|| 写个麻烦的