使用NUMTODSINTERVAL( date-sysdate ,'Day') 得到interval, DATE-SYSDATE
------------------------------
+000000337 00:03:46.000000000
+000001428 00:03:46.000000000
-000000027 23:56:14.000000000
+000000010 00:03:45.999999999
+000000036 00:03:45.999999999
+000000646 00:03:46.000000000
+000000646 00:03:46.000000000请问怎么才能格式化成 ------------------------------
+337 Day 00:03:46
.....etc的样式
------------------------------
+000000337 00:03:46.000000000
+000001428 00:03:46.000000000
-000000027 23:56:14.000000000
+000000010 00:03:45.999999999
+000000036 00:03:45.999999999
+000000646 00:03:46.000000000
+000000646 00:03:46.000000000请问怎么才能格式化成 ------------------------------
+337 Day 00:03:46
.....etc的样式
col month_btwn for a30;
col days for a35;
col months for a35;
select dt2-dt1,
months_between(dt2,dt1) months_btwn,
numtodsinterval(dt2-dt1,'day') days,
numtoyminterval(months_between(dt2,dt1),'month') months
from (select to_date('2000-02-29 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
to_date('2001-03-15 11:22:33','yyyy-mm-dd hh24:mi:ss') dt2
from dual ); DT2-DT1 MONTHS_BTWN DAYS MONTHS
---------- ----------- ----------------------------------- -----------------------------------
380.430903 12.5622872 +000000380 10:20:30.000000000 +000000001-00select numtoyminterval(months_between(dt2,dt1),'month') year_months,
numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1)) ), 'day' ) days_hours
from (select to_date('2000-02-29 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
to_date('2001-03-15 11:22:33','yyyy-mm-dd hh24:mi:ss') dt2
from dual );YEAR_MONTHS DAYS_HOURS
--------------------------------------------------------------------------- ----------------------------------
+000000001-00 +000000015 10:20:30.000000000