2

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

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
Xalion
  • 582
  • 8
  • 23
  • 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,234
  • 12
  • 86
  • 101
  • 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

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,650
  • 2
  • 14
  • 35
0

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,738
  • 2
  • 11
  • 32