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

1 comment:

  1. Vint Ceramic Art | TITNIA & TECHNOLOGY
    Explore an all 바카라 사이트 new “Vint Ceramic Art” project titanium metal trim on TITNIA bsjeon & TECHNOLOGY. Our team of sculptors https://jancasino.com/review/merit-casino/ and artists have created new aprcasino and

    ReplyDelete