Connect By Level

single SQL query which will return first day and last day of the month starting from today’s date till next 100 months.

Requirement: 

1-Apr-2013 30-Apr-2013

1-May-2013 31-May-2013

1-Jun-2013 30-Jun-2013

1-Jul-2013 31-Jul-2013

-------- ---------

-------- ---------

100th Record 100th Record

    Solution:


SELECT LEVEL,
       TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, LEVEL - 1), 'MM'), 'DD-MON-RRRR'),
       TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, LEVEL - 1)), 'DD-MON-RRRR')
FROM DUAL
CONNECT BY LEVEL <= 100;



We can get Last Day of month using LAST_DAY (SYSDATE) and First Day using TRUNC (SYSDATE, 'MM'). To repeat 100 times we can use CONNECT BY LEVEL