how to calculate the number of quarters between 2 dates?
471326Jan 9 2006 — edited Jan 11 2006Hi
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