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?

Answer 1

Can you try this?

Go to Tools> Preferences > Database > NLS and set the Date Format as MM/DD/YYYY HH24:MI:SS

Answer 2

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'

Answer 3

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".

Answer 4

From Tools > Preferences > Database > NLS Parameter and set Date Format as

DD-MON-RR HH:MI:SS

Answer 5

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; 

Answer 6

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!

Answer 7

Tools> Preferences > Database > NLS In my case there was "XFF" after seconds in "date format" row. After corrected parameter to "yyyy-mm-dd HH24:MI:SS" time was apear.

Answer 8

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