3

I use Oracle SQL Developer and Query Result doesn't return me full date. Also the same format (yyyy/mm/dd) in table -> data. How to fix this? I didnt find any option which would help.

enter image description here

Xalion
  • 623
  • 9
  • 27
  • 1
    Possible duplicate of [How can I set a custom date time format in Oracle SQL Developer?](https://stackoverflow.com/questions/8134493/how-can-i-set-a-custom-date-time-format-in-oracle-sql-developer) – radimpe May 29 '17 at 15:21

3 Answers3

7

It's just formatting issue. Goto "Tools->Preferences->Database->NLS" , set Date Format to something like "YYYY-MM-DD hh24:mi:ss" .

or you can run ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD hh24:mi:ss'

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • or include the date format you want in your query, that way app/nls parameters won't matter – thatjeffsmith May 30 '17 at 00:03
  • @thatjeffsmith : surely, `to_char` always work, but it will be a slight difference - in this case column value comes to client as string , not as datetime. That in turn might cause some client(e.g. SQLDeveloper ) functionality "misbehave". Say sorting data in grid by string column formatted as "DD-MM-YYYY hh24:mi:ss" is very different from sorting the same data by datetime column . – a1ex07 May 30 '17 at 14:56
  • i'm saying if you're doing up a report - don't leave date formats to chance – thatjeffsmith May 30 '17 at 16:53
1

That's because the default behaviour of SQL Developer is to ony show the date part. Try forcing the output to be displayed as a datetime string this way

select  id, id_player, id_map, x, y, to_char(time, 'yyyy/mm/dd hh24:mi:ss')
from LOGI
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
  • At least a real answer. I don't understand why the first one has been selected as a "DATE" column will ALWAYS be displayed as a ... (short) date... Thanks ! – CodeKiller Jun 13 '23 at 08:19
1

This is due to your NLS_DATE_FORMAT settings, which decide how a date is displayed on the client.

To change it permanently,

Go to Tool -> Preference -> Database -> NLS and set date format to YYYY/MM/DD HH24:MI:SS or whatever you want.

Utsav
  • 7,914
  • 2
  • 17
  • 38