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?

score 441 · Accepted Answer · edited Dec 02 '13 at 12:45

441

Can you try this?

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

edited Dec 02 '13 at 12:45

Muhammad Bekette

1,396
1
24
60

answered Sep 27 '12 at 19:13

rizalp1

6,346
2
17
19

1

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. – Xonatron Sep 27 '12 at 19:21
1

+1 thanks for helping me. But, I think it should be Tools > Prefrences > Database > NSL – Bhavik Shah Feb 22 '13 at 12:04
10

In the German version: Extra > Voreinstellungen > Datenbank > NLS – Olivier Faucheux Jul 24 '13 at 10:14
12

WOW... this was beautiful. Can't believe I was doing the conversion in the `select` statement. – Leniel Maccaferri Sep 21 '13 at 00:07
22

'Not Logical Setting' :-) – worldwidejimbo Dec 12 '14 at 10:39
6

No! It stands for National Language Support. It is used to define national date, number, currency and language settings. – user2441441 Mar 19 '15 at 16:38
4

Note: I needed to reconnect to the database for this setting to take effect – spume Jan 21 '16 at 12:22
3

Just open the 'Preferences' under 'Tools' menu, and search for ‘NLS.’ – Chacko Apr 08 '16 at 04:16
1

This should be set by default. Had a hard time with a buch of Dates without time information and _DISTINCT_ clause. +1! – Sergio A. Jan 30 '20 at 12:14

score 45 · Answer 2 · answered May 16 '15 at 12:32

45

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'

answered May 16 '15 at 12:32

Upendra Gughane

560
4
7

score 24 · Answer 3 · answered Jan 21 '16 at 21:09

24

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

answered Jan 21 '16 at 21:09

trevorsky

889
1
7
6

1

Thanks, this really helped me - I just copied the format from timestamp and couldn't understand why it wasn't working. – fleeblewidget Aug 16 '16 at 13:58
Is there a reason (other than standards) to use `YYYY-MM-DD HH24:MI:SS` versus `DD-MM-YYYY HH24:MI:SS` ? – Nathan Goings Mar 31 '20 at 20:33
2

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. – trevorsky Apr 01 '20 at 21:05

score 18 · Answer 4 · edited Dec 19 '18 at 18:17

18

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

DD-MON-RR HH:MI:SS

edited Dec 19 '18 at 18:17

gitsitgo

6,589
3
33
45

answered Sep 30 '13 at 12:50

gigz

942
2
9
26

score 5 · Answer 5 · answered Sep 27 '12 at 19:26

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;

score 4 · Answer 6 · answered Jun 21 '17 at 18:57

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!

score 4 · Answer 7 · answered Mar 15 '21 at 08:13

4

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.

answered Mar 15 '21 at 08:13

Сергей

41
1

score 0 · Answer 8 · answered Apr 18 '19 at 10:10

0

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

answered Apr 18 '19 at 10:10

Abhishek

1,558
16
28

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

8 Answers8

Linked