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.

Something I didn't know up TO_DATE

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

Look at this (my session date format is DD/MM/YYYY):

select to_date('2011','YYYY') d from dual;

D                   
---------------------
01-02-2011 00:00:00

select trunc(to_date('2011','YYYY'),'Y') d from dual;

D                   
---------------------
01-01-2011 00:00:00 

I didn't know that when converting a year from characters to a date, Oracle would return the value using the current month (in the SQL reference I can't find anything about this behavior).

I found this out because while working on some queries spanning over a 12 month period supposedly starting from the first month of the year, I could not understand why I was seeing a missing January 2011 and an unwanted January 2012 in the result set.

Initially I feared that this was something introduced with Oracle 11gR2, but I later verified that this happens on Oracle 10gR2 as well, so it must have always been like this.

Fortunately in my applications I never store date information as strings, so I don't really expect this to affect my software, but finding this at a customer's site, makes me think that I'll need to search each and every occurrence of this and replace it with the TRUNCated expression in order to make their queries consistent. 

You never stop learning.