SQL Developer is returning only the date not the time How do I fix this

Question

Here s what SQL Develoepr is giving me  both in the results window and when I export   CREATION TIME        -------------------  27-SEP-12 27-SEP-12 27-SEP-12   Here s what another piece of software running the same query db gives   CREATION TIME        -------------------  2012-09-27 14 44 46  2012-09-27 14 44 27  2012-09-27 14 43 53    How do I get SQL Developer to return the time too   Partial answer   select TO CHAR creation time  DD-Mon-YYYY HH24 MI SS   from    If anyone knows how to set the default to show the full information  please answer below

User · Answer

To expand on some of the previous answers, I found that Oracle DATE objects behave different from Oracle TIMESTAMP objects. In particular, if you set your NLS_DATE_FORMAT to include fractional seconds, the entire time portion is omitted.

Format "YYYY-MM-DD HH24:MI:SS" works as expected, for DATE and TIMESTAMP
Format "YYYY-MM-DD HH24:MI:SSXFF" displays just the date portion for DATE, works as expected for TIMESTAMP

My personal preference is to set DATE to "YYYY-MM-DD HH24:MI:SS", and to set TIMESTAMP to "YYYY-MM-DD HH24:MI:SSXFF".

User · Answer

This will get you the hours  minutes and second  hey presto   select   to char CREATION TIME  RRRR   year     to char CREATION TIME  MM   MONTH     to char CREATION TIME  DD   DAY     to char CREATION TIME  HH MM SS   TIME    sum bytes  Bytes  from    v datafile  group by    to char CREATION TIME  RRRR       to char CREATION TIME  MM       to char CREATION TIME  DD       to char CREATION TIME  HH MM SS     ORDER BY 1  2

User · Answer

Date format can also be set by using below query  -  alter SESSION  set NLS DATE FORMAT    date format   e g    alter SESSION set NLS DATE FORMAT    DD-MM-YYYY HH24 MI SS

User · Answer

From Tools   Preferences   Database   NLS Parameter  and set Date Format as  DD-MON-RR HH MI SS

User · Answer

Neither of these answers would work for me  not the Preferences NLS configuration option or the ALTER statement  This was the only approach that worked in my case   dbms session set nls  nls date format     DD-MM-YYYY HH24 MI SS         added after the BEGIN statement  I am using PL SQL Developer v9 03 1641  Hopefully this is of help to someone

User · Answer

Well I found this way        Oracle SQL Developer  Left top icon    Preferences   Database   NLS   and set the Date Format as MM DD YYYY HH24 MI SS

User · Answer

Can you try this   Go to Tools  Preferences   Database   NLS and set the Date Format as MM DD YYYY HH24 MI SS

[date] SQL Developer is returning only the date, not the time. How do I fix this?

Examples related to date

Examples related to datetime

Examples related to time

Examples related to oracle-sqldeveloper