PL/SQL: INTERVAL YEAR TO MONTH
483246Jan 16 2006 — edited Jan 17 2006I am utilizing INTERVALs to determine the number of years and the number of months between two dates. Using the example dates below, I was expecting the EXTRACT(YEAR FROM A) to return 4 years and the EXTRACT(MONTH FROM A) to return 11 months (at this point, the number of days is not important). However, 5 years and 0 months is being EXTRACTed. I don't understand why 4 years and 11 months is not being EXTRACTed instead...
Any help would be very appreciated. Thanks!
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit
SQL*Plus: Release 9.2.0.1.0
SET SERVEROUTPUT ON;
DECLARE
start_date DATE;
end_date DATE;
A INTERVAL YEAR TO MONTH;
B INTERVAL DAY (4) TO SECOND (9);
BEGIN
start_date := TO_DATE('07/01/1996','MM/DD/YYYY');
end_date := TO_DATE('06/20/2001','MM/DD/YYYY');
A :=( end_date - start_date ) YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE( A );
DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '||
EXTRACT(MONTH FROM A)||' Months' );
B :=( end_date - start_date ) DAY TO SECOND;
DBMS_OUTPUT.PUT_LINE( EXTRACT(DAY FROM B)||' Days');
A :=( TO_DATE('06/20/2001','MM/DD/YYYY') - TO_DATE('07/01/2000','MM/DD/YYYY') ) YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '|| EXTRACT(MONTH FROM A)||' Months' );
END;
The output is
+05-00
5 Years and 0 Months
1815 Days
1 Years and 0 Months
PL/SQL procedure successfully completed.