오라클에서 두 날짜 사이의 기간 구하기 ( 예: 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
최신 댓글