最近在做一个报表,数据库是用MYSQL,于是需要个日历表,
通常在做报表的时候会经常使用到。
代码
CREATE
DEFINER
=
'
root
'
@
'
localhost
'
PROCEDURE
`create_tmp_yearmonth_tb`()
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
#变量声明
declare sourcedate date;
declare targetdate date;
declare indexdate date;
declare index_month int ;
declare step_year_month char ( 20 );
set sourcedate = date( ' 2010-01-01 ' );
set targetdate = date( ' 2010-05-01 ' );
set indexdate = sourcedate;
set index_month = month (indexdate);
#执行循环
while indexdate <= targetdate do
begin
set index_month = month (indexdate);
set step_year_month = concat( cast ( year (indexdate) as char ) , ' - ' , cast (index_month as char ), ' -01 ' );
insert tmp_ym_tb(ym) values (step_year_month);
set step_year_month = concat( cast ( year (indexdate) as char ) , ' - ' , cast ((index_month + 1 ) as char ), ' -01 ' );
set indexdate = date(step_year_month);
end ;
end while ;
END ;
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
#变量声明
declare sourcedate date;
declare targetdate date;
declare indexdate date;
declare index_month int ;
declare step_year_month char ( 20 );
set sourcedate = date( ' 2010-01-01 ' );
set targetdate = date( ' 2010-05-01 ' );
set indexdate = sourcedate;
set index_month = month (indexdate);
#执行循环
while indexdate <= targetdate do
begin
set index_month = month (indexdate);
set step_year_month = concat( cast ( year (indexdate) as char ) , ' - ' , cast (index_month as char ), ' -01 ' );
insert tmp_ym_tb(ym) values (step_year_month);
set step_year_month = concat( cast ( year (indexdate) as char ) , ' - ' , cast ((index_month + 1 ) as char ), ' -01 ' );
set indexdate = date(step_year_month);
end ;
end while ;
END ;