Why the Time is missing?

Question

I have code below:

declare
  v_rst varchar2(200);

  cursor c_cur is
    select sysdate from dual;
begin

  open c_cur;
  fetch c_cur
    into v_rst;
  close c_cur;

  dbms_output.put_line(v_rst);

end;

the output result for it is 23-DEC-14

When I run below query, the result is 12/23/2014 11:21:06 AM:

select sysdate from dual;

I do not know why the TIME part is gone when I run the first code.

Answer 1

The time is not "gone". That format is just the default. You need to set the NLS_DATE_FORMAT for the default value:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH:MI:SS';

or whatever format your want.

Or, change your query to format the date field using the to_char function

 select to_char(sysdate, 'MM/DD/YYYY HH:MM:SS') from dual;

See the format model here.