下面这段代码是oracle中用于取得'20090401'到'20090430'间所有日期的SQL,现在我们需要一个mySQL的,由于本人实在不会mySQL,同时时间又紧,所以希望各位帮忙看看,mySQL下这个SQL怎么写!
select to_date('20090401', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date('20090430', 'yyyymmdd'), 'ddd')
select to_date('20090401', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date('20090430', 'yyyymmdd'), 'ddd')
num
1
2
3
..
...
SELECT DATE_ADD('20090401',INTERVAL num-1 DAY) AS `date`
FROM Numbers
WHERE DATE_ADD('20090401',INTERVAL num-1 DAY)<='20090430';
但,我给的oracle的SQL是以一个月为例,可是实际使用的时候可能是很长的时间段,所以用序号表Numbers
好像不大好,因为不好确定Numbers的最大值
drop procedure if exists report_date $$create procedure report_date(start_date date,end_date date)
begin
declare i int default 0;
create temporary table date_tb(`date` date) engine=memory; while(date_add(start_date,interval i day) <= end_date) do
insert into date_tb values(date_add(start_date,interval i day));
set i=i+1;
end while; select * from date_tb;end$$
delimiter ;
set @i='2009-04-01';
while @i<='2009-04-30' do
set @i=date_add(@I,interval 1 day);
select @i;
end while;