interval Oracle格式,oracle下interval类型的用法总结

什么都不说，先上例子：

select date '2010-01-01' + interval '21' year from dual

select date '2010-01-01' + interval '123-2' year(4) to month from dual

select date '2010-01-01' + interval '100' month from dual

select date '2010-01-01' + interval '100' day(2) from dual

select date '2010-01-01' + interval '1 2' day to hour from dual

select date '2010-01-01' + interval '1 2:3' day to minute from dual

select date '2010-01-01' + interval '1 2:3:4' day to second from dual

select date '2010-01-01' + interval '1:2:3' hour from dual

select date '2010-01-01' + interval '1:2' hour to minute from dual

select date '2010-01-01' + interval '1:2:3' hour to second from dual

select date '2010-01-01' + interval '1:2' minute to second from dual

转换函数：

select numtoyminterval(13, 'month'), numtodsinterval(100, 'hour') from dual

PLSQL中的定义及调用：

declare

v_dts interval day to second := interval '1 2:3:4' day to second;

v_dd date := date '2010-01-01';

begin

execute immediate 'alter session set nls_date_format = ''yyyy-mm-dd hh24:mi:ss''';

select v_dd + v_dts into v_dd from dual;

dbms_output.put_line(v_dd);

end;