Oracle. How to output date and time?

Question

My Visit table is the following:

insert into Visit
values(12, to_date('19-JUN-13', 'dd-mon-yy'), to_date('19-JUN-13 12:00 A.M.' , 'dd-mon-yy hh:mi A.M.'));
insert into Visit
values(15, to_date('20-JUN-13', 'dd-mon-yy'), to_date('20-JUN-13 02:00 A.M.' , 'dd-mon-yy hh:mi A.M.'));
insert into Visit
values(18, to_date('21-JUN-13', 'dd-mon-yy'), to_date('21-JUN-13 10:30 A.M.' , 'dd-mon-yy hh:mi A.M.'));

When i try to query it: select * from Visit i get:

SQL> select * from visit;

   SLOTNUM DATEVISIT ACTUALARRIVALTIME                                                                                                                                                                                                                                                                                                                                                                                 
---------- --------- ------------------------------                                                                                                                                                                                                                                                                                                                                                                     
        12 19-JUN-13 19-JUN-13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        15 20-JUN-13 20-JUN-13                                                                                                                                                                                                       
        18 21-JUN-13 21-JUN-13                                                                                                                                                                                                      

SQL> spool off;

How come the time is not there?

Answer 1

You can also set a format that applies to all dates like this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS PM';

That way, your original query would output the dates in the format you're after, without using TO_CHAR. To set back to the usual default format, just do this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

Answer 2

that is the oracle date format that is set as the default for your instance.

you should properly specify the format to see more or less.. something like this:

select to_char( datevisit, 'dd-mon-yy hh24:mi:ss' ) from visit