如何取下月所有时间

取出下个月所有日期，根据当前系统日期，取出下个月所有天
结果类似(如现在是5月)
-----------
2011-06-01 00:00:00.000
2011-06-02 00:00:00.000
2011-06-03 00:00:00.000
2011-06-04 00:00:00.000
2011-06-05 00:00:00.000
2011-06-06 00:00:00.000
2011-06-07 00:00:00.000
2011-06-08 00:00:00.000
2011-06-09 00:00:00.000
2011-06-10 00:00:00.000
2011-06-11 00:00:00.000
2011-06-12 00:00:00.000
2011-06-13 00:00:00.000
2011-06-14 00:00:00.000
2011-06-15 00:00:00.000
2011-06-16 00:00:00.000
2011-06-17 00:00:00.000
2011-06-18 00:00:00.000
2011-06-19 00:00:00.000
2011-06-20 00:00:00.000
2011-06-21 00:00:00.000
2011-06-22 00:00:00.000
2011-06-23 00:00:00.000
2011-06-24 00:00:00.000
2011-06-25 00:00:00.000
2011-06-26 00:00:00.000
2011-06-27 00:00:00.000
2011-06-28 00:00:00.000
2011-06-29 00:00:00.000
2011-06-30 00:00:00.000

SQL code

select dateadd(d,number,convert(varchar(8),dateadd(m,1,getdate()),120)+'01') 
from master..spt_values 
where type='p' 
and month(dateadd(d,number,convert(varchar(8),dateadd(m,1,getdate()),120)+'01'))=month(convert(varchar(8),dateadd(m,1,getdate()),120)+'01')
and number<31
/*
-----------------------
2011-07-01 00:00:00.000
2011-07-02 00:00:00.000
2011-07-03 00:00:00.000
2011-07-04 00:00:00.000
2011-07-05 00:00:00.000
2011-07-06 00:00:00.000
2011-07-07 00:00:00.000
2011-07-08 00:00:00.000
2011-07-09 00:00:00.000
2011-07-10 00:00:00.000
2011-07-11 00:00:00.000
2011-07-12 00:00:00.000
2011-07-13 00:00:00.000
2011-07-14 00:00:00.000
2011-07-15 00:00:00.000
2011-07-16 00:00:00.000
2011-07-17 00:00:00.000
2011-07-18 00:00:00.000
2011-07-19 00:00:00.000
2011-07-20 00:00:00.000
2011-07-21 00:00:00.000
2011-07-22 00:00:00.000
2011-07-23 00:00:00.000
2011-07-24 00:00:00.000
2011-07-25 00:00:00.000
2011-07-26 00:00:00.000
2011-07-27 00:00:00.000
2011-07-28 00:00:00.000
2011-07-29 00:00:00.000
2011-07-30 00:00:00.000
2011-07-31 00:00:00.000

(31 行受影响)

*/

SQL code

select dateadd(dd,number,convert(varchar(8),dateadd(month,1,getdate()),120)+'01') as 日期
from master..spt_values
where type='p' 
and dateadd(dd,number,convert(varchar(8),dateadd(month,1,getdate()),120)+'01')<
convert(varchar(8),dateadd(month,2,getdate()),120)+'01'

/*
日期
-----------------------
2011-07-01 00:00:00.000
2011-07-02 00:00:00.000
2011-07-03 00:00:00.000
2011-07-04 00:00:00.000
2011-07-05 00:00:00.000
2011-07-06 00:00:00.000
2011-07-07 00:00:00.000
2011-07-08 00:00:00.000
2011-07-09 00:00:00.000
2011-07-10 00:00:00.000
2011-07-11 00:00:00.000
2011-07-12 00:00:00.000
2011-07-13 00:00:00.000
2011-07-14 00:00:00.000
2011-07-15 00:00:00.000
2011-07-16 00:00:00.000
2011-07-17 00:00:00.000
2011-07-18 00:00:00.000
2011-07-19 00:00:00.000
2011-07-20 00:00:00.000
2011-07-21 00:00:00.000
2011-07-22 00:00:00.000
2011-07-23 00:00:00.000
2011-07-24 00:00:00.000
2011-07-25 00:00:00.000
2011-07-26 00:00:00.000
2011-07-27 00:00:00.000
2011-07-28 00:00:00.000
2011-07-29 00:00:00.000
2011-07-30 00:00:00.000
2011-07-31 00:00:00.000

(31 行受影响)

首先是获取当前月份，然后加1，再从master..spt_values
查询一个月的时间

SQL code

select
 dateadd(d,number,convert(varchar(8),dateadd(m,1,getdate()),120)+'01') 
from
 master..spt_values 
where
 month(dateadd(d,number,convert(varchar(8),dateadd(m,1,getdate()),120)+'01'))=month(convert(varchar(8),dateadd(m,1,getdate()),120)+'01')
and
 type='p' 
and
 number between 1 and 31