declare
l_start date := to_date('2015-04-29 01:02:03', 'yyyy-mm-dd hh24:mi:ss');
l_end date := to_date('2015-05-15 11:22:33', 'yyyy-mm-dd hh24:mi:ss');
l_ym interval year to month;
l_ds interval day to second;
l_year number;
l_month number;
l_day number;
l_hour number;
l_minute number;
l_second number;
l_str varchar2(100);
begin
l_ym := numtoyminterval(trunc(months_between(l_end, l_start)), 'month');
l_ds := numtodsinterval(l_end -
add_months(l_start,
trunc(months_between(l_end, l_start))),
'day');
select extract(year from l_ym), extract(month from l_ym)
into l_year, l_month
from dual;
select extract(day from l_ds),
extract(hour from l_ds),
extract(minute from l_ds),
extract(second from l_ds)
into l_day, l_hour, l_minute, l_second
from dual;
l_str := lpad(to_char(l_year), 4, '') || '年' ||
lpad(to_char(l_month), 2, '') || '月' ||
lpad(to_char(l_day), 2, '') || '天' || ' ' ||
lpad(to_char(l_hour), 2, '') || ':' ||
lpad(to_char(l_minute), 2, '') || ':' ||
lpad(to_char(l_second), 2, '');
dbms_output.put_line(l_str);
end;
