mysql 中取某时间段内的月份及周数

--  获取年月的存储过程
DELIMITER $$

DROP   PROCEDURE   IF   EXISTS  `radius`.`mygetyearmonth`$$

CREATE  DEFINER = `radius`@` % `  PROCEDURE  `mygetyearmonth`( IN  datebegin  int , IN  dateend  int )
BEGIN
    DECLARE  MidDate  datetime ;
        Set  MidDate  =  DATE_FORMAT(concat( left (datebegin, 4 ), ' - ' , right (datebegin, 2 ), ' -01 ' ), ' %Y-%m-01 00:00:00 ' );
        CREATE   TEMPORARY   TABLE  MyYearMonth(
        yearmonth  int
        );
        WHILE  EXTRACT(YEAR_MONTH  FROM  MidDate) <= dateend DO
             insert   into  MyYearMonth(yearmonth)  values  (EXTRACT(YEAR_MONTH  FROM  MidDate));
     Set  MidDate = DATE_ADD(MidDate, INTERVAL  1   month );
         END   WHILE  ;
         select   *   from  MyYearMonth;
        DROP   TEMPORARY   TABLE  MyYearMonth;
END $$

DELIMITER ;
-- 过程结束

调用执行存储过程
call mygetyearmonth( ' 200601 ' , ' 200702 ' )
返回结果集
" 200601 "
" 200602 "
" 200603 "
" 200604 "
" 200605 "
" 200606 "
" 200607 "
" 200608 "
" 200609 "
" 200610 "
" 200611 "
" 200612 "
" 200701 "
" 200702 "

--  获取某段时间包含多少周的存储过程
DELIMITER $$

DROP   PROCEDURE   IF   EXISTS  `radius`.`mygetyearweeks`$$

CREATE  DEFINER = `radius`@` % `  PROCEDURE  `mygetyearweeks`( IN  datebegin  datetime , IN  dateend  datetime )
BEGIN
         DECLARE  MidDate  datetime ;
         DECLARE  num  int ;
         Set  MidDate = DATE_FORMAT(date_add(datebegin,INTERVAL  - (DAYOFWEEK(datebegin) - 1 ) + 1   DAY ), ' %Y-%m-%d 00:00:00 ' );
         SET  num = 1 ;
         CREATE   TEMPORARY   TABLE  MyYearWeek(
        num  int ,
        begintime  datetime ,
        endtime  datetime
        );

     WHILE  MidDate < dateend DO
     insert   into  MyYearWeek(num,begintime,endtime)  values  
    (num,
    DATE_FORMAT(MidDate, ' %Y-%m-%d 00:00:00 ' ),
    DATE_FORMAT(DATE_ADD(MidDate, INTERVAL  6   DAY ), ' %Y-%m-%d 23:59:59 ' )
    );
         Set  MidDate = DATE_ADD(MidDate, INTERVAL  7   DAY );
         Set  num = num + 1 ;
     END   WHILE  ;

        select   *   from  MyYearWeek;
         DROP   TEMPORARY   table  MyYearWeek ;
         END $$

DELIMITER ;
-- 过程结束
调用执行存储过程
call mygetyearweeks( ' 2008-01-03 ' , ' 2008-04-09 ' )
返回结果集
num,begintime,endtime
" 1 "," 2007 - 12 - 31   00 : 00 : 00 "," 2008 - 01 - 06   23 : 59 : 59 "
" 2 "," 2008 - 01 - 07   00 : 00 : 00 "," 2008 - 01 - 13   23 : 59 : 59 "
" 3 "," 2008 - 01 - 14   00 : 00 : 00 "," 2008 - 01 - 20   23 : 59 : 59 "
" 4 "," 2008 - 01 - 21   00 : 00 : 00 "," 2008 - 01 - 27   23 : 59 : 59 "
" 5 "," 2008 - 01 - 28   00 : 00 : 00 "," 2008 - 02 - 03   23 : 59 : 59 "
" 6 "," 2008 - 02 - 04   00 : 00 : 00 "," 2008 - 02 - 10   23 : 59 : 59 "
" 7 "," 2008 - 02 - 11   00 : 00 : 00 "," 2008 - 02 - 17   23 : 59 : 59 "
" 8 "," 2008 - 02 - 18   00 : 00 : 00 "," 2008 - 02 - 24   23 : 59 : 59 "
" 9 "," 2008 - 02 - 25   00 : 00 : 00 "," 2008 - 03 - 02   23 : 59 : 59 "
" 10 "," 2008 - 03 - 03   00 : 00 : 00 "," 2008 - 03 - 09   23 : 59 : 59 "
" 11 "," 2008 - 03 - 10   00 : 00 : 00 "," 2008 - 03 - 16   23 : 59 : 59 "
" 12 "," 2008 - 03 - 17   00 : 00 : 00 "," 2008 - 03 - 23   23 : 59 : 59 "
" 13 "," 2008 - 03 - 24   00 : 00 : 00 "," 2008 - 03 - 30   23 : 59 : 59 "
" 14 "," 2008 - 03 - 31   00 : 00 : 00 "," 2008 - 04 - 06   23 : 59 : 59 "
" 15 "," 2008 - 04 - 07   00 : 00 : 00 "," 2008 - 04 - 13   23 : 59 : 59 "