Thursday, May 16, 2019

JSON get_Date() method always discards time portion

Always check out the original article at 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 
set serveroutput on
  l_job_description   JSON_OBJECT_T;
  l_job_params        JSON_ARRAY_T;
  l_job_arr_elem      JSON_OBJECT_T;
  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
        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;

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

Method get_Timestamp() will come to the rescue.

1 comment:

Unknown said...

Thank you so much! Was driving me crazy.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles