1 declare
2
3 l_start date := to_date('2015-04-29 01:02:03', 'yyyy-mm-dd hh24:mi:ss');
4 l_end date := to_date('2015-05-15 11:22:33', 'yyyy-mm-dd hh24:mi:ss');
5
6 l_ym interval year to month;
7 l_ds interval day to second;
8 l_year number;
9 l_month number;
10 l_day number;
11 l_hour number;
12 l_minute number;
13 l_second number;
14 l_str varchar2(100);
15 begin
16 l_ym := numtoyminterval(trunc(months_between(l_end, l_start)), 'month');
17 l_ds := numtodsinterval(l_end -
18 add_months(l_start,
19 trunc(months_between(l_end, l_start))),
20 'day');
21
22 select extract(year from l_ym), extract(month from l_ym)
23 into l_year, l_month
24 from dual;
25
26 select extract(day from l_ds),
27 extract(hour from l_ds),
28 extract(minute from l_ds),
29 extract(second from l_ds)
30 into l_day, l_hour, l_minute, l_second
31 from dual;
32
33 l_str := lpad(to_char(l_year), 4, '0') || '年' ||
34 lpad(to_char(l_month), 2, '0') || '月' ||
35 lpad(to_char(l_day), 2, '0') || '天' || ' ' ||
36 lpad(to_char(l_hour), 2, '0') || ':' ||
37 lpad(to_char(l_minute), 2, '0') || ':' ||
38 lpad(to_char(l_second), 2, '0');
39
40 dbms_output.put_line(l_str);
41
42 end;
