This discussion is archived
1 Reply Latest reply: Oct 21, 2013 5:49 AM by fac586 RSS

Unable to store time including date

SAYYED Newbie
Currently Being Moderated

Dear experts,

 

I am having hidden item called P252_UPDATE_DATE, the  value of this item assigning through page process and updating in USER table through auto update process.

 

when i execute below mentioned update statement from background(sql developer) it is updating exact date and time as 21-10-2013 20:15:12.

 

but when i execute through apex it is updating(auto update) as 21-10-2013 00:00:00.

 

Backend Code:

update user set last_update_date=TO_DATE( to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')       where user_id=21;

 

Apex code:

       BEGIN

          Select TO_DATE( to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')  INTO  :P252_UPDATE_DATE FROM DUAL;       

       END;

 

Please suggest me how can i update exact sysdate value by hidden item in apex 4.2.2.

 

Thanks and regards,

Ibrahim Sayyed.

  • 1. Re: Unable to store time including date
    fac586 Guru
    Currently Being Moderated

    SAYYED wrote:

     

    Dear experts,

     

    I am having hidden item called P252_UPDATE_DATE, the  value of this item assigning through page process and updating in USER table through auto update process.

     

    when i execute below mentioned update statement from background(sql developer) it is updating exact date and time as 21-10-2013 20:15:12.

     

    but when i execute through apex it is updating(auto update) as 21-10-2013 00:00:00.

     

    Backend Code:

    update user set last_update_date=TO_DATE( to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')       where user_id=21;

     

    Apex code:

           BEGIN

              Select TO_DATE( to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')  INTO  :P252_UPDATE_DATE FROM DUAL;     

           END;

     

    Please suggest me how can i update exact sysdate value by hidden item in apex 4.2.2.

     

    All APEX items are strings. Assigning a DATE value to the :P252_UPDATE_DATE item therefore requires an implicit conversion using the default date format (which will not include the time components) to turn the date value into a string. Remove the outer TO_DATE to avoid the unnecessary implicit conversion.

     

    However, note that this type of audit column is better handled using a PL/SQL API or database trigger rather than in APEX.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points