manipulation of date between two dates

[satheesh_color]

Hi All,

I have startdate and enddate as an incoming columns, based on which i would like to get everymonths between the given dates.

StartDate:10/18/2014
EndDate:03/22/2015

Expected output:
1-10-2014
1-11-2014
1-12-2014
1-01-2015
1-02-2015
1-03-2015

Kindly let me know your thoughts on the same.


Thanks,
Satheesh.R

[thompsonp]

Do you have a database available? Which one?

[satheesh_color]

Yes..we do have Oracle10g.



Thanks,
Satheesh.R

[anbu]

Code: Select all

SELECT SRC_TBL.StartDate,
    SRC_TBL.EndDate,
    ADD_MONTHS(TRUNC(SRC_TBL.StartDate,'MM'),RNO-1) MTH_STRT_DT
  FROM SRC_TBL
  LEFT OUTER JOIN
    (SELECT ROWNUM RNO FROM DUAL CONNECT BY LEVEL <= 240
    ) RW
  ON 1 = 1
  WHERE ADD_MONTHS(TRUNC(StartDate,'MM'),RNO-1) <= EndDate

[thompsonp]

or you could take a look at Oracle's Pipelined Table Functions

[chulett]

Or transformer looping if you have 8.5 or higher.