34,588
社区成员
发帖
与我相关
我的任务
分享
DECLARE @BDATE DATETIME,@EDATE DATETIME
SELECT @BDATE='2009-2-5',@EDATE='2009-10-21'
DECLARE @BDATE DATETIME,@EDATE DATETIME
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 行受影响)
*/
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 行受影响)