Using oracle apex dynamic action to set a date time value that is concatenated

I have a date value in one page item and i have a time value in another. i want to concat the date with the time and set it to another item using a dynamic action. Structure as below

P_DATE = ’01-01-2021′ (item is a date with format mask dd-mm-yyyy)

P_TIME = ’08:30 AM’ (item is date with format mask HH:MIAM)

Query:

select to_date(to_char(to_date(P_DATE ,'DD-MON-YYYY'), 'DD-MON-YYYY') || ' '|| 
to_char(P_TIME,'HH:MIAM'),'DD-MON-YYYY HH:MIAM') a
from dual;

Desired Outcome: 01-01-2021 08:30 AM

Dynamic action is on change the P_DATE item then from an sql query, concat the P_DATE and P_TIME and set it to P_VALUE

When i run the select in sql developer with hardcoded values then it returns the correct stuff but when i try to set the value in the item with the concat date it giving me invalid number error sometimes and not a valid month.

Can you suggest the corrected way or an alternative way of doing this (maybe use a function)

THank you.

Answer

What do you mean by “they are date items” ? Are they of the type “Date Picker” , do they have a source column of type “DATE” in a form, or do they map to a DATE column in the database ?

In APEX, all page items are basically strings. The frontend of the web application doesn’t know about oracle datatypes so everything is treated as a plain string and during the processing the conversion is done. So that is how you should treat the page items, not as DATE data type like you would in SQL or PL/SQL. To concatenate a date string and a time string, you can just a plain concatenate without TO_CHAR. This can be done in plain PL/SQL, no need to SELECT FROM DUAL – that is just an unnecessary call to the SQL engine.

This is the “true” action on the change of P_VALUE. Tested on 21.1, so depending on your version there might be some attribute naming differences but it works the same.

Action: execute server side code.
Source:

:P_VALUE := :P_DATE ||' '||:P_TIME

Items to submit: P_DATE, P_TIME.
Items to return: P_VALUE

Since you’re dealing with strings there is room for error here, you’ll have to ensure proper error handling if the user input does not exactly match the format since that could generate invalid date values.