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

date datetime time oracle-sqldeveloper

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?

Muhammad Bekette

Can you try this?

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

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

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

'Not Logical Setting' :-)

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

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

Upendra Gughane

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'

trevorsky

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

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

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

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.

gitsitgo

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

DD-MON-RR HH:MI:SS

derek

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;

Canica

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!

Сергей

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.

Abhishek

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

https://i.stack.imgur.com/haMhW.png

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US