Oracle常用日期函數

常用的時間格式掩碼如下：
掩碼元素       含義
YYYY       四位元年份 （如：2005）     year
YY         二位元年份（如  05）
Q          季度（1-4）
MM         月份（01-12）      month
WW         年的星期數（1-53），其中第一星期為年的第一天至第七天
W          月的星期數（1-5），其中第一星期為月的第一天至第七天
DDD        年的日（1-366）
DD         月的日（1-31）    
D          周的日（1-7），其中周日為1，周六為7    day
HH24       24小時制（0-23）     hour
MI         分鐘（0-59）     minute
SS         秒（0-59）     second
SSSSS      自午夜之後的秒（0-86399）


兩個很不錯的函數，可以操作日期，也可以運算元值:
        round(日期,'指定的日期掩碼')      返回日期時間的四捨五入結果
        指定的掩碼不同則結果結果也不同
    year           以 7月1日分界線
    month        以16號為分界線
    day            以中午12點為分界線
    hh             以30分鐘為分界線
    mi             以30秒為分界線

        trunc(日期,'指定的日期掩碼')        返回截斷時間
    year            本年度的1月1日
    month        本月的1號
    
例子：(注意: day    d    dd 三者的區別)
SQL> select round(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'year') from dual;
ROUND(TO_DATE('2007051616:31:4
------------------------------
2007-1-1
 
SQL> select round(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'month') from dual;
ROUND(TO_DATE('2007051616:31:4
------------------------------
2007-6-1
 
SQL> select round(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'mm') from dual;
ROUND(TO_DATE('2007051616:31:4
------------------------------
2007-6-1
 
SQL> select round(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'day') from dual;
ROUND(TO_DATE('2007051616:31:4
------------------------------
2007-5-20
 
SQL> select round(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'d') from dual;
ROUND(TO_DATE('2007051616:31:4
------------------------------
2007-5-20
 
SQL> select round(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'dd') from dual;
ROUND(TO_DATE('2007051616:31:4
------------------------------
2007-5-17

SQL> select trunc(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'year') from dual;
TRUNC(TO_DATE('2007051616:31:4
------------------------------
2007-1-1
 
SQL> select trunc(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'month') from dual;
TRUNC(TO_DATE('2007051616:31:4
------------------------------
2007-5-1
 
SQL> select trunc(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'mm') from dual;
TRUNC(TO_DATE('2007051616:31:4
------------------------------
2007-5-1
 
SQL> select trunc(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'day') from dual; 
TRUNC(TO_DATE('2007051616:31:4
------------------------------
2007-5-13
 
SQL> select trunc(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'d') from dual;
TRUNC(TO_DATE('2007051616:31:4
------------------------------
2007-5-13
 
SQL> select trunc(to_date('20070516 16:31:46','yyyymmdd hh24:mi:ss'),'dd') from dual;
TRUNC(TO_DATE('2007051616:31:4
------------------------------
2007-5-16

日期時間函數：
           add_months(日期，number)    指定日期延遲number個月
           last_day(日期)   指定日期當月的最後一天
           new_time(日期，時區簡寫)   調整時區
           next_day(日期，number)  number表示周幾，星期日是1，指定number的日期（一周內或一周后）
           months_between(日期1，日期2)   日期1和日期2之間有幾個月
           sysdate   系統當期那日期和時間
練習時的代碼：

select to_number(to_char(last_day(add_months(to_date('20040406','yyyymmdd'),-1))+1,'yyyymmdd')) from dual;
 
TO_NUMBER(TO_CHAR(LAST_DAY(ADD
------------------------------
                      20040401

select to_number(to_char(last_day(to_date('20040406','yyyymmdd')),'yyyymmdd')) from dual;

TO_NUMBER(TO_CHAR(LAST_DAY(ADD
------------------------------
                      20040430

create or replace procedure p_hkb_date_insert is
  /*
  過程功能描述：日期插入表中
  */

  v_days number(10);
  v_date date;
  i      number(10);

begin

  begin
    --取得當月天數
    select to_number(to_char(last_day(sysdate), 'dd'))
      into v_days
      from dual;
    --select sysdate from dual; 當前日期
    --select last_day(sysdate) from dual;  月底日期
    --select last_day(add_months(sysdate, -1)) from dual;   上月底日期
    -- SELECT   to_char(last_day(SYSDATE),'dd')   days   FROM   dual;    當前月的天數
    --select last_day(add_months(sysdate,-1))+1 from dual; 當前月第一天
    --select to_number(to_char(sysdate,'yyyymmdd')) from dual;系統當前日期轉換成如20070910格式：
 
  end;
  i := 1;
  begin
    select last_day(add_months(sysdate, -1)) into v_date from dual;
    while i <= v_days
   
     loop
      insert into hkb_date
      values
        (v_date + i,
         to_char(v_date + i, 'yyyymmdd'),
         to_number(to_char(v_date + i, 'yyyymmdd')));
      --insert into hkb_date
      --(float_date)
      -- values
      --(to_char(v_date + i, 'yyyymmdd'));
      -- insert into hkb_date
      --(number_date)
      -- values
      --(to_number(to_char(v_date + i, 'yyyymmdd')));
      i := i + 1;
    end loop;
  end;
end p_hkb_date_insert;

create table hkb_date_construct as select * from hkb_date where 1=2;  繼承表欄位

create table hkb_date_data as select * from hkb_date;  繼承表記錄

1.日期時間間隔操作

目前時間減去7分鐘的時間
select sysdate,sysdate - interval '7' MINUTE from dual
目前時間減去7小時的時間
select sysdate - interval '7' hour from dual
目前時間減去7天的時間
select sysdate - interval '7' day from dual
目前時間減去7月的時間
select sysdate,sysdate - interval '7' month from dual
目前時間減去7年的時間
select sysdate,sysdate - interval '7' year from dual
時間間隔乘以一個數字
select sysdate,sysdate - 8 *interval '2' hour from dual

2.日期到字元操作
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
參考oracle的相關關文檔(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)

3. 字元到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具體用法和上面的to_char差不多。

4. trunk/ ROUND函數的使用
select trunc(sysdate ,'YEAR') from dual
select trunc(sysdate ) from dual
select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual

5.oracle有毫秒級的資料類型
--返回目前時間 年月日小時分秒毫秒
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回當前 時間的秒毫秒，可以指定秒後面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;

6.計算程式啟動並執行時間(ms)
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for I in 1 .. 1000
loop
open l_rc for
'select object_name from all_objects '||

'where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;