在SQL SERVER 中获取指定连续月份的旬的日期范围

有时，要用到按旬统计，还可能涉及不存在记录的旬(为0)，必须要有一个能获得连续数月的旬的日期范围的子查询作连接用，可以按如下方法处理：

 

declare @dt1 varchar(7), @dt2 varchar(7)
set @dt1='2011-07'
set @dt2='2011-09'

select convert(varchar(7),dateadd(m,a.number,@dt1+'-01'),120)+(case b.number when 0 then ' 上旬' when 1 then ' 中旬' else ' 下旬' end)dt,
dateadd(d,b.number*10,dateadd(m,a.number,@dt1+'-01')) dt1, 
(case when b.number<2 then dateadd(d,b.number*10+9,dateadd(m,a.number,@dt1+'-01'))else dateadd(d,-1,dateadd(m,a.number+1,@dt1+'-01')) end) dt2
from master..spt_values a,master..spt_values b
where a.type='p' and b.type='p' and 
dateadd(m,a.number,@dt1+'-01')<=@dt2+'-01' and b.number<3
order by 2
/*
dt           dt1                     dt2
------------ ----------------------- -----------------------
2011-07 上旬   2011-07-01 00:00:00.000 2011-07-10 00:00:00.000
2011-07 中旬   2011-07-11 00:00:00.000 2011-07-20 00:00:00.000
2011-07 下旬   2011-07-21 00:00:00.000 2011-07-31 00:00:00.000
2011-08 上旬   2011-08-01 00:00:00.000 2011-08-10 00:00:00.000
2011-08 中旬   2011-08-11 00:00:00.000 2011-08-20 00:00:00.000
2011-08 下旬   2011-08-21 00:00:00.000 2011-08-31 00:00:00.000
2011-09 上旬   2011-09-01 00:00:00.000 2011-09-10 00:00:00.000
2011-09 中旬   2011-09-11 00:00:00.000 2011-09-20 00:00:00.000
2011-09 下旬   2011-09-21 00:00:00.000 2011-09-30 00:00:00.000

(9 行受影响)

*/