求助,这条SQL怎么写?

Question

我要查询出两个时间段之间的日期,比如20070801到20070804,用dual表写一个SQL
show出20070801,20070802,20070803,20070804,这个应该怎么写?

Answer 1

select * from dual where datecolumns between 20070801 and 20070804???

Answer 2

SQL>  select to_char(firstdate+rownum-1,'yyyymmdd') as "date"
  2     from (select to_date('20070801', 'yyyymmdd') as firstdate from dual),
  3          (select rownum rn
  4             from all_objects
  5            where rownum <= ('20070804' - '20070801') + 1);date
--------
20070801
20070802
20070803
20070804

Answer 3

SELECT  level,to_date(mindt,'yyyymmdd') + LEVEL -1 AS oper_date
         FROM    ( SELECT MIN(oper_date) mindt
                        , MAX(oper_date) maxdt
                   FROM  (SELECT '20070801' oper_date  FROM dual 
                         UNION ALL
                         SELECT '20070804' oper_date  FROM dual
                                ) mhe_foo
                 )
         CONNECT BY to_date(mindt,'yyyymmdd') + LEVEL -1 <= to_date(maxdt,'yyyymmdd')