developers

    Forum Stats

  • 3,874,067 Users
  • 2,266,672 Discussions
  • 7,911,721 Comments

Discussions

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

Bausdev
Bausdev Member Posts: 1 Red 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)

Best Answer

  • Justin Warwick
    Justin Warwick Member Posts: 123 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);

Answers

  • Justin Warwick
    Justin Warwick Member Posts: 123 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: 850 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


developers