Topic
  • 3 replies
  • Latest Post - ‏2013-02-07T15:41:28Z by DPotterSQL
DPotterSQL
DPotterSQL
57 Posts

Pinned topic Difference in Months and Days Between Two Dates

‏2013-02-06T19:28:51Z |
All,

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.

Thanks,

DPotterSQL
Updated on 2013-02-07T15:41:28Z at 2013-02-07T15:41:28Z by DPotterSQL
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Difference in Months and Days Between Two Dates

    ‏2013-02-06T23:28:34Z  
    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.

    Cheers
    Prasan
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Difference in Months and Days Between Two Dates

    ‏2013-02-07T14:09:03Z  
    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
    DPotterSQL
    57 Posts

    Re: Difference in Months and Days Between Two Dates

    ‏2013-02-07T15:41:28Z  
    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!