SQL Fun : Connect By Level

Yesterday evening while watching Chris Gayle’s blistering knock against Mumbai Indians, my friend Sarkar came up with a requirement. He asked me to write a 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





Result:

He is happy now  J