JSON get_Date() method always discards time portion

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.


In case you wonder why you don't see the time portion when you call the method get_Date() on a JSON object:

-- tested on Oracle 12.2.0.1.0 
set serveroutput on
declare
  l_job_description   JSON_OBJECT_T;
  l_job_params        JSON_ARRAY_T;
  l_job_arr_elem      JSON_OBJECT_T;
begin
  l_job_description := JSON_OBJECT_T.parse(
'{
  "parameters" : [
                  {"name":"p_date", "type":"date", "value":"2019-12-31T12:01:59"},
                  {"name":"p_dat2", "type":"date", "value":"2019-12-31T12:01:59"}
                 ]
 }');
  l_job_params := l_job_description.get_Array('parameters');
  
  if l_job_params is not null then

    if l_job_params.get_Size > 0 then
    
      for i in 0..l_job_params.get_Size - 1
      loop
  
        l_job_arr_elem := JSON_OBJECT_T(l_job_params.get(i));
        case l_job_arr_elem.get_String('name')
        when 'p_date' then
          dbms_output.put_line(to_char(l_job_arr_elem.get_Date('value'),'YYYY-MM-DD HH24:MI:SS'));
        when 'p_dat2' then
          dbms_output.put_line(to_char(l_job_arr_elem.get_Timestamp('value'),'YYYY-MM-DD HH24:MI:SS'));
        end case;
  
      end loop;
    
    end if;

  end if;

end;
/

2019-12-31 00:00:00
2019-12-31 12:01:59

Method get_Timestamp() will come to the rescue.