Forum Stats

  • 3,770,491 Users
  • 2,253,125 Discussions
  • 7,875,485 Comments

Discussions

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

User_ZNWSZ
User_ZNWSZ Member Posts: 1 Green Ribbon

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)

Answers

  • Justin Warwick
    Justin Warwick Member Posts: 119 Bronze Badge

    You may be able to do this client side with javascript, instead of SQL. Would that work for you? Also, exactly what Item types are you using? If you are actually using a datepicker (or select list) vs typing in dates and times, that might have an impact on your options.

    In the most out-of-the-box way (aside from what you have specified so far), you could: create True action for the change event which would be of type "Execute JavaScript Code".

    Code:

    var combined_datetime;

    combined_datetime = apex.item("P6_DATE").getValue() + " " + apex.item("P6_TIME").getValue();

    apex.item("P6_CONCATENATED_DATETIME").setValue(combined_datetime);

  • AndyH
    AndyH Member Posts: 749 Bronze Trophy
    edited May 2, 2021 8:45PM

    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;
    
    • The date format that you specify doesn't match the one that you use in your query;
    • You've got a random use of to_char and to_date in your concatenation - all page items are strings stored as strings;
      • e.g. try to_char(to_date(:P_DATE || ' '||:P_TIME , 'DD-MM-YYYY HH:MIAM'), 'DD-MM-YYYY HH:MIAM');
      • make sure you use the defined date formats;
    • SQL Query isn't needed - try set value using PL/SQL Expression