Wednesday, April 2, 2014

how to get the first_date and last_date of the months between the given time period

how to get the first_date and last_date of the months between the given time period

We have a start date and end date given as an input .We have to calculate the number of months and start date ,end date of each month.
The following query is used to calculate the above requirement:-

SELECT ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) start_date
,last_day(ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) ) end_date
FROM   DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)
    <= TRUNC(TO_DATE('29-Jun-2014', 'DD-MON-YYYY'), 'MON')


and the output will be like:-
start_date end_date

01/03/2014|31/03/2014
01/04/2014|30/04/2014
01/05/2014|31/05/2014
01/06/2014|30/06/2014