I am working with an oracle database and need to find the months and days between two different dates. Example: 4 Months and 5 Days.
The problem is that I can't find a pl/sql function that will give me both the months and days. I know that you can simply subtract one date column from the other to find the days between and that there is a months_between function but even that one only gives me a 4.25 sort of value instead of separating off the days.
What can I do to have Cognos Report Studio produce a months and days between value like the one mentioned above.
Pinned topic Difference in Months and Days Between Two Dates
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-07T15:41:28Z at 2013-02-07T15:41:28Z by DPotterSQL
SystemAdmin 110000D4XK1959 Posts
Re: Difference in Months and Days Between Two Dates2013-02-06T23:28:34ZThis is the accepted answer. This is the accepted answer.Hi,
I think you can use the function "_ymdint_between" to get the desired result. This function can be found under the Business Date/Time Functions.
DanielWagemann 2700058MAJ850 Posts
Re: Difference in Months and Days Between Two Dates2013-02-07T14:09:03ZThis is the accepted answer. This is the accepted answer.At first I thought we could get away with using a combination of the cast interval functions, but it appears its a bit more difficult than that.
Attached please find a sample 10.1.1 sample spec against the Go Sales(query) relational package. The final step would have been to add the month interval to the day interval to get the different. Howevever this will give you a data type mis-match.
You may need to create a string field which concats all the pieces together.
cast(MonthValue,char(4)) +' months and ' cast(Dayvalue,char(4) + ' days'.
Attached is the spec that does the bulk of the work.
DPotterSQL 270003P4C762 Posts
Re: Difference in Months and Days Between Two Dates2013-02-07T15:41:28ZThis is the accepted answer. This is the accepted answer.It looks like in order to make the first work I would have to dissect the returned YYYYMMDD format in order to get the '__ years and __ months' but that wouldn't be that hard.
The second appears to work nicely as well though.
Thank You Both!