日期有关的查询

一年的第一天，最后一天

SQL> select trunc(sysdate,'y'),last_day(add_months(trunc(sysdate,'yyyy'),11)) from dual;

TRUNC(SYS LAST_DAY(
--------- ---------
01-JAN-13 31-DEC-13

一年一共多少天

SQL> select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;

ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y')
----------------------------------------------------
                                                 365

第几周

SQL> select to_char(sysdate,'iw') from dual;

TO
--
42

星期几

SQL> select to_char(sysdate,'dy') from dual;

TO_CHA
------
sat

具体时间

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-10-19 03:50:44

日历

  with x
    as (
 select *
   from (
 select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
        to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
        to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
        to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
        to_char(sysdate,'mm') mth
   from dual
  connect by level <= 31
        )
  where curr_mth = mth
 )
 select max(case dw when 2 then dm end) Mo,
        max(case dw when 3 then dm end) Tu,
        max(case dw when 4 then dm end) We,
        max(case dw when 5 then dm end) Th,
        max(case dw when 6 then dm end) Fr,
        max(case dw when 7 then dm end) Sa,
        max(case dw when 1 then dm end) Su
   from x
  group by wk
  order by wk;

MO TU WE TH FR SA SU
-- -- -- -- -- -- --
   01 02 03 04 05 06
07 08 09 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31