← Workbench · Notes

List the months between two dates - PL/SQL

PL SQL Query to list the months between two dates

SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE('01-JAN-22','DD-MON-YY'), 'MM'), LEVEL -1),'MON-YY')
MONTH_YEAR 
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE('01-DEC-22','DD-MON-YY'), 
    TO_DATE('01-JAN -22','DD-MON-YY')) + 1
ORDER BY LEVEL;

Result set

MONTH_YEAR
JAN-22
FEB-22
MAR-22
APR-22
MAY-22
JUN-22
JUL-22
AUG-22
SEP-22
OCT-22
NOV-22
DEC-22

Keep reading

See all

Essay

Useful PL/SQL queries

In this post, I’m sharing some handy PL/SQL queries that help with monitoring and maintaining Oracle databases...

Read

Note

NOT IN vs NOT EXISTS in PL/SQL

SELECT * FROM TABLE1 WHERE ID NOT IN ( SELECT ID FROM TABLE2 ) When I ran the above query I know the condition will give me the results, because the data exists …

Read

Note

To extract the value of a tag from an XML-like string in PL/SQL

To extract the value of a tag from the given XML-like string in PL/SQL, you can use the EXTRACTVALUE function or XMLTABLE for XML processing. Example Using …

Read