SQL Developer is returning only the date, not the time. How do I fix this?
DateDatetimeTimeOracle SqldeveloperDate Problem Overview
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?
Date Solutions
Solution 1 - Date
Can you try this?
Go to Tools> Preferences > Database > NLS and set the Date Format as MM/DD/YYYY HH24:MI:SS
Solution 2 - Date
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'
Solution 3 - Date
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".
Solution 4 - Date
From Tools > Preferences > Database > NLS Parameter and set Date Format as
DD-MON-RR HH:MI:SS
Solution 5 - Date
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;
Solution 6 - Date
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!
Solution 7 - Date
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.
Solution 8 - Date
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
