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

end date rule question

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?

• 1. Re: end date rule question
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
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
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
Currently Being Moderated

Thankyou Jasmine!!

• 5. Re: end date rule question
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?

• 6. Re: end date rule question
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
Currently Being Moderated

Doh -- thanks!

Legend

• Correct Answers - 10 points