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!

how to calculate the number of quarters between 2 dates?

471326Jan 9 2006 — edited Jan 11 2006
Hi

I want to calculate the number of quarters between the 2 dates.

For example:
-------------------
quarters between '01-jan-04','31-mar-07' = 13 quarters...
--------------------------------------------------------------------------------------
but the below function only gives = 12...


CREATE OR REPLACE FUNCTION quarters_between (early_date date, late_date date)
RETURN number IS
first_date number;
last_date number;
interim number;
difference number;
BEGIN
first_date := to_char(early_date,'yyyy');
last_date := to_char(late_date,'yyyy');
interim := nvl(last_date - first_date,0);
IF (interim != 0)
THEN interim := interim * 4;
END IF;
first_date := to_char(early_date,'q');
last_date := to_char(late_date,'q');
difference := (last_date - first_date) + interim;
RETURN difference;
END quarters_between;

----------------------------------------------------------------------------------------------------
SELECT quarters_between('01-jan-04','31-mar-07') FROM dual;

12
-----------------------------------------------------------------------------------------------

But the answer is 13..

Could some one help me around how to acheive this..?

Thanks in advance!

regards
-preetha
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 8 2006
Added on Jan 9 2006
13 comments
7,375 views