top of page

How to get Months between 2 dates in PLSQL

  • Writer: Rumesh Aponso (RMAX)
    Rumesh Aponso (RMAX)
  • Nov 5, 2024
  • 1 min read

Updated: Nov 22, 2024

WAY 1

SELECT MONTHS_BETWEEN(TRUNC(TO_DATE('2021-12-01-00.00.00', 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN')),
                      TRUNC(TO_DATE('2021-10-01-00.00.00', 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN')))   no_of_months
FROM   DUAL;

Output:

ree

WAY 2

DECLARE
   str_out_     VARCHAR2(32000);
   from_date_   DATE;
   to_date_     DATE;

   CURSOR add_month IS
      SELECT ADD_MONTHS(from_date_, 1)
      FROM   DUAL;
BEGIN
   from_date_ := TO_DATE('2021-08-01-00.00.00','YYYY-MM-DD-HH24.MI.SS','NLS_CALENDAR=GREGORIAN');

   to_date_   := TO_DATE('2021-12-21-00.00.00','YYYY-MM-DD-HH24.MI.SS','NLS_CALENDAR=GREGORIAN');
   
   str_out_ := '''' || TO_CHAR(from_date_, 'FMMON-YY') || '''';

   OPEN  add_month;
   FETCH add_month INTO from_date_;
   CLOSE add_month;

   WHILE from_date_ <= to_date_
   LOOP
      str_out_ := str_out_ || ', ''' || TO_CHAR(from_date_, 'FMMON-YY') || '''';

      OPEN  add_month;
      FETCH add_month INTO from_date_;
      CLOSE add_month;
   END LOOP;

   Dbms_Output.Put_Line(str_out_);
END;

Output:

ree
ree

Comments


Copyright © 2025 RMAXOneNote

  • Online CV
  • LinkedIn
  • Youtube
  • GitHub
  • Blogger
bottom of page