对于from date,to date这种形式的查找.......

SQL> select * from test;

FIRST_DATE LAST_DATE
------------------- -------------------
2007-04-10 00:00:00 2007-04-16 00:00:00

SQL> select all_date
2 from (select trunc(FIRST_DATE, 'MON') + n all_date, FIRST_DATE, LAST_DATE
3 from (select rownum - 1 n from dual connect by level <= 31), TEST)
4 where (all_date < FIRST_DATE or all_date > LAST_DATE )
5 and trunc(all_date, 'MON') = trunc(LAST_DATE, 'MON');

ALL_DATE
-------------------
2007-04-01 00:00:00
2007-04-02 00:00:00
2007-04-03 00:00:00
2007-04-04 00:00:00
2007-04-05 00:00:00
2007-04-06 00:00:00
2007-04-07 00:00:00
2007-04-08 00:00:00
2007-04-09 00:00:00
2007-04-17 00:00:00
2007-04-18 00:00:00

ALL_DATE
-------------------
2007-04-19 00:00:00
2007-04-20 00:00:00
2007-04-21 00:00:00
2007-04-22 00:00:00
2007-04-23 00:00:00
2007-04-24 00:00:00
2007-04-25 00:00:00
2007-04-26 00:00:00
2007-04-27 00:00:00
2007-04-28 00:00:00
2007-04-29 00:00:00

ALL_DATE
-------------------
2007-04-30 00:00:00

23 rows selected.

ITPub博客

查找丢失的日子

最新文章