Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Converting the value of a REST JSON element

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
Answers
-
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 :)