Calculating month-wise total days between two dates

This script returns the month-wise total number of days between dates given in parameters.

DECLARE

   dt1            DATE;

   dt2            DATE;

   dt             DATE;

   days           NUMBER := 0;

   no_of_months   NUMBER;

BEGIN

   SELECT CEIL (  MONTHS_BETWEEN (TO_DATE (   ’01-‘

                                           || TO_CHAR (TO_DATE (:date1),

                                                       ‘MON-RRRR’

                                                      )

                                          ),

                                  LAST_DAY (TO_DATE (:date2))

                                 )

                * (-1)

               )

     INTO no_of_months

     FROM DUAL;

 

   SELECT TO_DATE (:date1), TO_DATE (:date2)

     INTO dt1, dt2

     FROM DUAL;

 

   dt := dt1;

   DBMS_OUTPUT.put_line (‘—————————-‘);

 

   FOR dy IN 1 .. no_of_months

   LOOP

      IF dy = 1

      THEN

         days := LAST_DAY (dt1) – dt1 + 1;

      ELSIF dy = no_of_months

      THEN

         days := dt2 – TO_DATE (’01-‘ || TO_CHAR (dt2, ‘MON-RRRR’));

      ELSE

         days := TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE (dt)), ‘DD’));

      END IF;

 

      DBMS_OUTPUT.put_line (TO_CHAR (dt, ‘MON-RR’) || ‘ Days: ‘ || days);

      dt := ADD_MONTHS (dt, 1);

   END LOOP;

 

   DBMS_OUTPUT.put_line (‘—————————-‘);

END;

Advertisements