Simple Date Arithmetic in Oracle APEX

1
votes

What I need to do is, in my mind, incredibly simple. So simple in fact that it is probably obvious and therefore I cannot seem to find any place that documents how to do this.

What I need is just to take the value of a Date Picker page Item (:P1_DATE_1) and through a dynamic action set the value of a second Date Picker page Item (:P1_DATE_2). The value of (:P1_DATE_2) should be 2 years, or 730 days, greater than the value of (:P1_DATE_1). So all I need is a simple '+730' expression right?

I had this working using sysdate as the start date for this calculation. The below PL/SQL expression gave the appropriate output:

    to_char(sysdate + 730,'dd-MON-rr')

But then I could not get it to translate to accepting the value of the page item. I already have the actions and everything set up I just can't get it to function when I try substituting :P1_DATE_1 for sysdate. I have tried as many different manifestations of that expression using the page item but it does not populate the 2nd page item.

Sorry for the probably stupid question but if anyone could help out I would appreciate it. Thanks!

2
oracleplsqldatepickeroracle-apex
if you want exactly 2 years then to_char(add_months(sysdate,24),'dd-MON-rr') is better – Ben
Thats all well and good but I do not want to use sysdate. I want to use the date in P1_DATE_1 as the date being modified. The method I posted for sysdate works fine – Spags

2 Answers

2
votes

@Justin Cave is correct: all values in page items are treated as varchar2

As for your dynamic action, this is what i've set up: Region:

items

Dynamic action (date 2 add):

dynamic action

True action:

true action

1
votes

Are you getting an error? If so, what error? Since page items in APEX are always strings, you need to convert them to dates before doing date arithmetic on them. Something like this should work assuming that P1_DATE_1 is in the DD-MON-RR format as well.

to_char( add_months( to_date( :P1_DATE_1, 'DD-MON-RR' ),
                     24 ),
         'DD-MON-RR' )

If you're not getting an error and the second item just isn't defaulting, my guess is that you have an order of operations problem. Are you certain that the first item is actually set to a non-NULL value when the initialization code for the second item is run? If you're trying to set the value in the second item based on a value that the human selects at runtime in the first date picker, you'd either need to submit the page or you'd need a bit of Javascript.