Search This Blog

Friday, July 29, 2022

Months between 2 dates in Oracle PL/SQL

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')))
FROM   DUAL;

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

No comments:

Post a Comment

Read file content from an Oracle Directory in PL/SQL/ in IFS

Read single file DECLARE file_handle_ UTL_FILE.FILE_TYPE; directory_name_ CONSTANT all_directories.directory_name%TYPE := '...

Popular Posts