Skip to Main Content

Application Development Software


For appeals, questions and feedback, please email

Converting the value of a REST JSON element

DeanMJan 28 2021

So I am working with REST in APEX 20.2 running on ORACLE 18c, and I am having a problem converting a timestamp stored in milliseconds to a readable DateTime format (YYYY-MM-DD HH24:MI:SS).
The REST call is working and it returns the datetime in milliseconds. I know the SQL to convert the milliseconds into a valid Oracle DateTime format - that's not my question. You can see the function I'm using in the screenshot below, but the full text is: to_char(to_date('1970-01-01 00', 'yyyy-mm-dd hh24') + (1611605111450)/1000/60/60/24, 'YYYY-MM-DD HH24:MI:SS').
What I'm having trouble with is doing the actual conversion in the REST call itself, using APEX REST Data services (previously Web Source Modules). The function above has a hard-coded millisecond value (1611605111450) which works, but obviously this is not what I need since this will return the same datetime for every record.
I need to replace the hard-coded millisecond time with the value of the element itself (in this case RECORD_DATETIME).
Does anyone know how to perform this correctly in APEX 20.2? I've tried using colon variable notation (:RECORD_DATETIME and :dateTime) but those did not work.
I'm banging my head against the wall!
Thanks for your help in advance! Next round will be on me...

Post Details
Added on Jan 28 2021
1 comment