7 Replies Latest reply: Feb 19, 2014 10:50 AM by user36 RSS

end date rule question

user36 Newbie
Currently Being Moderated

Hi All,

 

I have a requirement to calculate an end date which is 35 days plus any additional days left at the end of the month, my question is - is there a way of calculating the number of remaining days in a month for any given month?

 

Many thanks in advance

  • 1. Re: end date rule question
    Baris Yildirim Guru
    Currently Being Moderated

    Hi,

     

    if I understood correctly,

     

    let's say, your date is to_Date('15/01/2014','DD/MM/YYYY')

     

     

    SELECT LAST_dAY(to_Date('15/01/2014','DD/MM/YYYY'))-to_Date('15/01/2014','DD/MM/YYYY') FROM dual

     

    Regards

  • 2. Re: end date rule question
    user36 Newbie
    Currently Being Moderated

    Hi Baris - I mean how can i write these rules in OPM. - Oracle Policy Modelling. Many thanks

  • 3. Re: end date rule question
    Jasmine Lee Expert
    Currently Being Moderated

    Here's one way to do it.

     

    Extract the year and month from 'the start date':


    the year of the start date = ExtractYear(the start date)

    the month of the start date = ExtractMonth(the start date)


    Construct a date value which is the start of the month in which 'the start date' falls:


    the start of the month in which the start date falls = MakeDate(the year of the start date, the month of the start date, 1)


    Find the end of the month by adding 1 month to the date above, then subtracting 1 day:


    the end of the month in which the start date falls = AddDays(AddMonths(the start of the month in which the start date falls, 1), -1)


    Find the number of days from the start date to the end of the month. Depending on whether 'the start date' itself and the end date of the month are meant to be included/excluded from the count, you may want to experiment with DayDifferenceInclusive, DayDifferenceExclusive.


    the number of days from the start date to the end of the month = DayDifference(the start date, the end of the month in which the start date falls)


    Cheers,

    Jasmine

  • 4. Re: end date rule question
    user36 Newbie
    Currently Being Moderated

    Thankyou Jasmine!!

  • 5. Re: end date rule question
    user36 Newbie
    Currently Being Moderated

    I actually have a follow up question -

     

    So once I have the number of days from the start date to the end of the month

    I know I need to add 35 days plus whatever the number of days is above to the date which a change was reported.


    But, addDays is what I was thinking of using, but this is not a static number, this is 35 days + the number of days from the start date to the end of the month...

     

    So how do I get to that bit?

     

    Thanks in advance!

  • 6. Re: end date rule question
    Jasmine Lee Expert
    Currently Being Moderated

    That's the easy bit.

     

    the due date = AddDays(the start date, 35 + the number of days from the start date to the end of the month)

     

    Cheers,

    Jasmine

  • 7. Re: end date rule question
    user36 Newbie
    Currently Being Moderated

    Doh -- thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points