Hi,
I have below PL/SQL code. I am formatting a date in YYYY-MM-DDTHH:MI:SS.SSSZ format and inserting into a table. This works fine in SQL Developer.
When same is done in a Oracle Apex 18c report generated from PL/SQL returning a query, then time portion disappears and shows up as '2020-05-04T::.000Z.
Is there a way to keep time component when used in Apex ? Any help is welcome
create table my_table(bname varchar2(30),date1 timestamp, date2 timestamp)
Data in above table --->'AAAAA',18-FEB-19 07.20.44.000000000 AM,28-FEB-20 12.06.41.000000000 AM
create table dest_table(bname varchar2(30),from_time varchar2(30),from_time varchar2(30));
declare
l_kURL varchar2(3000);
l_query varchar2(3000);
l_url varchar2(3000);
cursor kBname is
select to_char(to_date(substr(date1,1,9),'DD-MON-YY'),'YYYY-MM-DD')||'T'||substr(date1,11,2)||':'||substr(date1,14,2)||':'||substr(date1,17,2)||'.'||'000Z' from_time,
to_char(to_date(substr(date2,1,9),'DD-MON-YY'),'YYYY-MM-DD')||'T'||substr(date2,11,2)||':'||substr(date2,14,2)||':'||substr(date2,17,2)||'.'||'000Z' to_time,
bname
from my_table;
r kBname%rowtype;
begin
open kBname;
fetch kBname int r;
close kBname;
insert into dest_table values(r.bname,r.from_time,r.to_time);
commit;
end;Thanks,
Girish
You don't need SUBSTR functions to get timestamp formatting. TO_CHAR can do it all for you, for example
SQL> select to_char(systimestamp,'DD-MON-YYYY HH24:MI:SS.FF9') from dual;
TO_CHAR(SYSTIMESTAMP,'DD-MON-YYYYHH24:M
---------------------------------------
12-MAY-2020 17:21:40.805000000
The reason I say this is that in APEX should query return a TIMESTAMP and then *in APEX* you can nominate the format mask that you want the data to be rendered in.