declare @Date DateTime
    declare @i int =1
    set @Date= Convert(char(10),dateadd(dd,-day(getdate())+@i,getdate()),120)
    while @Date<Convert(char(10),dateadd(dd,-day(getdate()),dateadd(m,1,getdate())),120)

    begin





            
        begin
        set @Date= Convert(char(10),dateadd(dd,-day(getdate())+@i,getdate()),120)
        print Convert(char(10),@Date,120)
        set @i=@i+1
        end
            
    end    

declare @sdate datetime 
declare @edate datetime 
set @sdate = '2009-8-30' 
set @edate = '2009-9-5' 


select 
    dateadd(dd,num,@sdate) 
from 
    (select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a 
where 
    dateadd(dd,num,@sdate) <=@edate 

/* 
                                                      
------------------------------------------------------ 
2009-08-30 00:00:00.000 
2009-08-31 00:00:00.000 
2009-09-01 00:00:00.000 
2009-09-02 00:00:00.000 
2009-09-03 00:00:00.000 
2009-09-04 00:00:00.000 
2009-09-05 00:00:00.000 

（所影响的行数为 7 行） 
*/

--功能：找出在2个日期之间的日期
--startdate:2009年9月15日  endDate:2009年10月3日 

declare @startdate datetime,@enddate datetime
set @startdate='2009-08-30'
set @enddate='2009-09-05'

select convert(varchar(10),dateadd(day,number,@startdate),120) 
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>0 
    and type='p'

/*----------
2009-08-31
2009-09-01
2009-09-02
2009-09-03
2009-09-04
2009-09-05

(6 行受影响)

/*



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587448.aspx

------解决方案--------------------
具体什么意思

------解决方案--------------------
SQL code
   declare @Date NVARCHAR(10)
   SET @Date ='2011-10'
SELECT
    CAST(@Date+'-01' AS DATETIME)+number AS 天
FROM master.dbo.spt_values AS a
WHERE type='P' AND CAST(@Date+'-01' AS DATETIME)+number<DATEADD(m,1,CAST(@Date+'-01' AS DATETIME))

------解决方案--------------------
SQL code
 DECLARE @Date NVARCHAR(10)
 SET @Date = '2011-10' ;
 WITH   cte
          AS ( SELECT   CAST(@Date + '-01' AS DATETIME) dt
               UNION ALL
               SELECT   DATEADD(DAY, 1, dt)
               FROM     cte
               WHERE    DATEADD(DAY, 1, dt) < DATEADD(m, 1,
                                                      CAST(@Date + '-01' AS DATETIME))
             )
    SELECT  *
    FROM    cte
    /*
    dt
-----------------------
2011-10-01 00:00:00.000
2011-10-02 00:00:00.000
2011-10-03 00:00:00.000
2011-10-04 00:00:00.000
2011-10-05 00:00:00.000
2011-10-06 00:00:00.000
2011-10-07 00:00:00.000
2011-10-08 00:00:00.000
2011-10-09 00:00:00.000
2011-10-10 00:00:00.000
2011-10-11 00:00:00.000
2011-10-12 00:00:00.000
2011-10-13 00:00:00.000
2011-10-14 00:00:00.000
2011-10-15 00:00:00.000
2011-10-16 00:00:00.000
2011-10-17 00:00:00.000
2011-10-18 00:00:00.000
2011-10-19 00:00:00.000
2011-10-20 00:00:00.000
2011-10-21 00:00:00.000
2011-10-22 00:00:00.000
2011-10-23 00:00:00.000
2011-10-24 00:00:00.000