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
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