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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,301 views