DECLARE @BDATE DATETIME,@EDATE DATETIME
SELECT @BDATE='2009-2-5',@EDATE='2009-10-21'
查询指定时间段内每个月的天数,怎么写好呢?我的思路似乎有点麻烦,求简洁的思路和代码
SELECT @BDATE='2009-2-5',@EDATE='2009-10-21'
查询指定时间段内每个月的天数,怎么写好呢?我的思路似乎有点麻烦,求简洁的思路和代码
2009-02 XX
2009-03 XX
.....
2009-10 XX这样的
DECLARE @BDATE DATETIME,@EDATE DATETIME
SELECT @BDATE='2009-2-5',@EDATE='2009-10-21'select mon=convert(varchar(7),dateadd(day,number,@bdate),120),[day]=count(*)
from master..spt_values s
where type='p' and dateadd(day,number,@bdate)<=@EDATE
group by convert(varchar(7),dateadd(day,number,@bdate),120)mon day
------- -----------
2009-02 24
2009-03 31
2009-04 30
2009-05 31
2009-06 30
2009-07 31
2009-08 31
2009-09 30
2009-10 21(9 行受影响)
SELECT @BDATE='2009-2-5',@EDATE='2009-10-21'SELECT
CONVERT(varchar(7),DATEADD(month,number,CONVERT(varchar(7),@BDATE,120)+'-01'),120) AS YearMonth,
DATEDIFF(day,DATEADD(month,number,CONVERT(varchar(7),@BDATE,120)+'-01'),
DATEADD(month,1,DATEADD(month,number,CONVERT(varchar(7),@BDATE,120)+'-01'))) AS days
FROM master.dbo.spt_values
WHERE type = 'p'
AND DATEADD(month,number,CONVERT(varchar(7),@BDATE,120)+'-01')
<= CONVERT(varchar(7),@EDATE,120)+'-01'/*
YearMonth days
--------- -----------
2009-02 28
2009-03 31
2009-04 30
2009-05 31
2009-06 30
2009-07 31
2009-08 31
2009-09 30
2009-10 31(9 行受影响)
*/
梁哥那个也有参考价值,都COPY回去学习,谢谢,结账~~
为什么我在sql2005查询分析器中直接运行就不出结果呢
初学,不太懂