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