Converting the value of a REST JSON element

DeanM

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...

DeanM

DeanM

Okay, I solved the problem by adding a second column, where I do the conversion, referencing the original column with the milliseconds in it, and left the original column alone. That worked!

Problem solved :)