存储过程中传入月份（如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 datetimeset @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-312009-09-012009-09-022009-09-032009-09-042009-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 aWHERE 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.0002011-10-02 00:00:00.0002011-10-03 00:00:00.0002011-10-04 00:00:00.0002011-10-05 00:00:00.0002011-10-06 00:00:00.0002011-10-07 00:00:00.0002011-10-08 00:00:00.0002011-10-09 00:00:00.0002011-10-10 00:00:00.0002011-10-11 00:00:00.0002011-10-12 00:00:00.0002011-10-13 00:00:00.0002011-10-14 00:00:00.0002011-10-15 00:00:00.0002011-10-16 00:00:00.0002011-10-17 00:00:00.0002011-10-18 00:00:00.0002011-10-19 00:00:00.0002011-10-20 00:00:00.0002011-10-21 00:00:00.0002011-10-22 00:00:00.0002011-10-23 00:00:00.0002011-10-24 00:00:00.0002011-10-25 00:00:00.0002011-10-26 00:00:00.0002011-10-27 00:00:00.0002011-10-28 00:00:00.0002011-10-29 00:00:00.0002011-10-30 00:00:00.0002011-10-31 00:00:00.000    */ 
  

 

查看更多 下一篇