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?

Nosology

Answer 1

Can you try this?

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

Sanitation

Perfect. Thank you! I ended up using MON. DD, YYYY HH24:MI:SS, notice the MON, to spell out the month to avoid confusion of dates like 06/07/12. – Nosology

+1 thanks for helping me. But, I think it should be Tools > Prefrences > Database > NSL – Noella

In the German version: Extra > Voreinstellungen > Datenbank > NLS – Centring

WOW... this was beautiful. Can't believe I was doing the conversion in the select statement. – Bender

'Not Logical Setting' :-) – Mitchum

No! It stands for National Language Support. It is used to define national date, number, currency and language settings. – Oren

Note: I needed to reconnect to the database for this setting to take effect – Atwater

Just open the 'Preferences' under 'Tools' menu, and search for ‘NLS.’ – Churchwarden

This should be set by default. Had a hard time with a buch of Dates without time information and DISTINCT clause. +1! – Snapper

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'

Pyuria

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

Dabster

Thanks, this really helped me - I just copied the format from timestamp and couldn't understand why it wasn't working. – Deliciadelicious

Is there a reason (other than standards) to use YYYY-MM-DD HH24:MI:SS versus DD-MM-YYYY HH24:MI:SS ? – Unsearchable

I always put the year first to avoid confusion. In the US, the typical sequence is MM-DD-YYYY. Elsewhere it's DD-MM-YYYY. Trouble is, you can't tell them apart for days between 1 and 12. So I always put year first. And I've never run across anything that puts year first without then also following with month then day. It also sorts cleanly as a string, which is a handy side effect. – Dabster

Answer 4

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

DD-MON-RR HH:MI:SS

Typhus

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; 

Troxell

Answer 6

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.

Frizzle

Answer 7

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!

Pentastich

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

Hollowell

Answer 9

It is not a SQL Oracle developer tool issue, you will face the same issue in fetching the query result.

So In the select query, use the like below when you are fetching the query.

SELECT to_char(CREATION_DATE,'DD:MM:RRRR HH:MM:SS') CREATION_DATE

Output:

25:10:2023 09:10:30

Moschatel