오라클에서 두 날짜 사이의 기간 구하기 ( 예: 1개월 5일 )

select trunc(months_between(to_date(:date2, 'yyyymmdd'), to_date(:date1, 'yyyymmdd')), 0) as month
       ,add_months( to_date(:date2, 'yyyymmdd'), - months_between(to_date(:date2, 'yyyymmdd'), to_date(:date1, 'yyyymmdd'))) - to_date(:date1, 'yyyymmdd') as day
  from dual;


SELECT  TO_DATE('20120125 11:00:00', 'YYYYMMDD HH:MI:SS')
        - TO_DATE('20120120 11:00:00', 'YYYYMMDD HH:MI:SS') as day_diff
  FROM  dual


 SELECT  TO_DATE('20120125 11:00:00', 'YYYYMMDD HH:MI:SS')
       - TO_DATE('20120120 10:00:00', 'YYYYMMDD HH:MI:SS') day_diff
  FROM   dual
  
SELECT  trunc(MONTHS_BETWEEN(TO_DATE('20120325', 'YYYYMMDD'), TO_DATE('20120101', 'YYYYMMDD'))) num_months
       ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days
FROM   dual


SELECT  TRUNC((TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) / 30) num_months
      ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days
FROM   dual

SELECT  TO_DATE('20120101', 'YYYYMMDD') start_date
       ,TO_DATE('20120325', 'YYYYMMDD') end_date
       ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days
       ,EXTRACT(MONTH FROM TO_DATE('20120101', 'YYYYMMDD')) start_month
       ,EXTRACT(MONTH FROM TO_DATE('20120325', 'YYYYMMDD')) end_month
       ,(EXTRACT(MONTH FROM TO_DATE('20120325', 'YYYYMMDD')) - EXTRACT(MONTH FROM TO_DATE('20120101', 'YYYYMMDD'))) diff_in_months
FROM   dual



SELECT  TRUNC(MONTHS_BETWEEN(TO_DATE('20120325', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD')))
       ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days
FROM   dual


SELECT  (TO_DATE('20120525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days
        ,TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD')))
        ,TRUNC(TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) / 12) num_years
        ,MOD(TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))), 12) num_months
  FROM   dual


SELECT  TO_DATE('20100101', 'YYYYMMDD') start_date
       ,TO_DATE('20120525', 'YYYYMMDD') end_date
       ,(TO_DATE('20120525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days
       ,EXTRACT(YEAR FROM TO_DATE('20100101', 'YYYYMMDD')) start_year
       ,EXTRACT(YEAR FROM TO_DATE('20120525', 'YYYYMMDD')) end_year
       ,EXTRACT(MONTH FROM TO_DATE('20100101', 'YYYYMMDD')) start_month
       ,EXTRACT(MONTH FROM TO_DATE('20120525', 'YYYYMMDD')) end_month
       ,(EXTRACT(YEAR FROM TO_DATE('20120525', 'YYYYMMDD')) - EXTRACT(YEAR FROM TO_DATE('20100101', 'YYYYMMDD'))) diff_in_years
       ,(EXTRACT(MONTH FROM TO_DATE('20120525', 'YYYYMMDD')) - EXTRACT(MONTH FROM TO_DATE('20100101', 'YYYYMMDD'))) diff_in_months
FROM   dual


 SELECT  x.start_date
        , x.end_date
        ,(x.end_date - x.start_date) diff_in_days
        ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_months
   FROM   (
            SELECT TO_DATE('20100101', 'YYYYMMDD') start_date
                  ,TO_DATE('20120515', 'YYYYMMDD') end_date
              FROM   dual
           ) x
           


 SELECT  y.start_date
        ,y.end_date
        ,y.diff_in_days
        ,y.diff_in_interval
        ,EXTRACT(YEAR FROM diff_in_interval) num_years
        ,EXTRACT(MONTH FROM diff_in_interval) num_months
   FROM (
         SELECT x.start_date
               ,x.end_date
               ,(x.end_date - x.start_date) diff_in_days
               ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_interval
          FROM (
                SELECT TO_DATE('20100101', 'YYYYMMDD') start_date
                      ,TO_DATE('20120515', 'YYYYMMDD') end_date
          FROM   dual
                ) x
         ) y