Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL: INTERVAL YEAR TO MONTH

483246Jan 16 2006 — edited Jan 17 2006
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 14 2006
Added on Jan 16 2006
7 comments
3,909 views