应用示例:
1、与指定日期在同一周,周1的日期是:
SQL> select to_date('2007-12-6', 'yyyy-mm-dd')
2 - to_char(to_date('2007-12-6', 'yyyy-mm-dd'), 'd') + 2
3 from dual;
TO_DATE('2
----------
2007-12-03
SQL> select trunc(to_date('2007-12-6', 'yyyy-mm-dd'), 'd') + 1 rn from dual;
TRUNC(TO_DATE('2007-12-6','YYY
------------------------------
2007-12-3
SQL> select next_day(to_date('2007-12-6', 'yyyy-mm-dd') - 7, '星期一') from dual;
NEXT_DAY(TO_DATE('2007-12-6','
------------------------------
2007-12-3
2、指定日期当月最后一个星期天:
SQL> select max(rn)
2 from (select to_date('2007-12-' || rownum, 'yyyy-mm-dd') rn
3 from dual
4 connect by rownum <=
5 (to_char(to_date('2007-12-31', 'yyyy-mm-dd'), 'dd'))) a
6 where to_char(a.rn, 'd') = 1
7 ;
MAX(RN)
-----------
2007-12-30
SQL> select next_day(trunc(to_date('2007-12-31', 'yyyy-mm-dd'), 'month')+31-7,'星期日') from dual;
NEXT_DAY(TRUNC(TO_DATE('2007-1
------------------------------
2007-12-30
3、今年第127天是几号:
SQL> select to_date('127','ddd') from dual;
TO_DATE('1
----------
2007-05-07