存储过程中传入月份（如2011-10），遍历一个月份中的每一天,该怎么解决

存储过程中传入月份（如2011-10），遍历一个月份中的每一天
目前只能实现遍历当前月份的，怎么实现遍历任何一个月份的？

请sql版高手指点，谢谢

SQL code

    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

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

------解决方案--------------------

SQL code

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 行受影响)

/*



本文来自****博客，转载请标明出处：http://blog.****.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
2011-10-25 00:00:00.000
2011-10-26 00:00:00.000
2011-10-27 00:00:00.000
2011-10-28 00:00:00.000
2011-10-29 00:00:00.000
2011-10-30 00:00:00.000
2011-10-31 00:00:00.000
    */

存储过程中传入月份（如2011-10），遍历一个月份中的每一天,该怎么解决

相关推荐