Exact Months/Years/Quarter etc.. between two dates by Stored Procedure

We generally face issue while deriving Number of years/Months and Quarter etc. between 2 DATES.  Below is the code I have implemented for one of my application , which I am sharing.

We can use the “_SYS_BI”.”M_TIME_DIMENSION” table in various other way, but this is one of its way of using and calling that where ever necessary.

/********* Begin Procedure Script ************/
BEGIN
IF UPPER(:IP_TYPE) = 'M'
 then
 var1 =  SELECT
 COUNT(*)-1 as VAL
FROM

 (

 SELECT DISTINCT YEAR, MONTH FROM "_SYS_BI"."M_TIME_DIMENSION"

 WHERE DATE_SQL BETWEEN :IP_DATE1 AND :IP_DATE2

 );
ELSEIF UPPER(:IP_TYPE) = 'Q'
 then
 var1 =  SELECT
 COUNT(*)-1 as VAL
FROM

 (

 SELECT DISTINCT YEAR,QUARTER FROM "_SYS_BI"."M_TIME_DIMENSION"

 WHERE DATE_SQL BETWEEN :IP_DATE1 AND :IP_DATE2

 );
ELSEIF UPPER(:IP_TYPE) = 'Y'
 then
 var1 =  SELECT
 COUNT(*)-1  as VAL
 FROM

 (

 SELECT DISTINCT YEAR FROM "_SYS_BI"."M_TIME_DIMENSION"

 WHERE DATE_SQL BETWEEN :IP_DATE1 AND :IP_DATE2

 );
END IF;
OUTPUT_TABLE = select * from :var1;
END;
 /********* End Procedure Script ************/

After activating the SP, we can call from SQL console and it will show the output as below.

call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','m',NULL);
call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','q',NULL);
call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','y',NULL);
call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','M',NULL);
call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','Q',NULL);
call "_SYS_BIC"."users.Chandan/SP_TEST_TIME_DIM"('2015-03-01','2016-03-01','Y',NULL);

The output we are going to get is as below.

Hope it will helps!

I welcome any upgradation on this. 🙂