By default, Oracle SQL developer displays date values as 15-NOV-11. I would like to see the time part (hour/minute/second) by default.
Is there a way to configure this within Oracle SQL Developer?
This question is related to
oracle
oracle-sqldeveloper
The answer is
In my case the format set in Preferences/Database/NLS was [Date Format] = RRRR-MM-DD HH24:MI:SSXFF but in grid there were seen 8probably default format RRRR/MM/DD (even without time) The format has changed after changing the setting [Date Format] to: RRRR-MM-DD HH24:MI:SS (without 'XFF' at the end).
There were no errors, but format with xff at the end didn't work.
Note: in polish notation RRRR means YYYY
When i copied the date format for timestamp and used that for date, it did not work. But changing the date format to this (DD-MON-YY HH12:MI:SS AM) worked for me.
The change has to be made in Tools->Preferences-> search for NLS
SQL Developer Version 4.1.0.19
Step 1: Go to Tools -> Preferences
Step 2: Select Database -> NLS
Step 3: Go to Date Format and Enter DD-MON-RR HH24: MI: SS
Step 4: Click OK.
With Oracle SQL Developer 3.2.20.09, i managed to set the custom format for the type DATE this way :
In : Tools > Preferences > Database > NLS
Or : Outils > Préférences > Base de donées > NLS
YYYY-MM-DD HH24:MI:SS
Note that the following format does not worked for me :
DD-MON-RR HH24:MI:SS
As a result, it keeps the default format, without any error.
For anyone still having an issue with this; I happened to find this page from Google...
Put it in like this (NLS Parameters)... it sticks for me in SQLDeveloper v3.0.04:
DD-MON-YY HH12:MI:SS AM or for 24-Hour, DD-MON-YY HH24:MI:SS
Goto to Tools > Preferences. In the tree, select Database > NLS. There are three Date/Time formats available: Date, Timestamp and Timestamp TZ. Editing the Date format gives the desired effect.
Like I have said above; this approach has not given me a permanent change.
I stumbled on this post while trying to change the display format for dates in sql-developer. Just wanted to add to this what I found out:
- To Change the default display format, I would use the steps provided by ousoo i.e Tools > Preferences > ...
But a lot of times, I just want to retain the DEFAULT_FORMAT while modifying the format only during a bunch of related queries. That's when I would change the format of the session with the following:
alter SESSION set NLS_DATE_FORMAT = 'my_required_date_format'
Eg:
alter SESSION set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'
I have a related issue which I solved and wanted to let folks know about my solution. Using SQL Developer I exported from one database to csv, then tried to import it into another database. I kept getting an error in my date fields. My date fields were in the Timestamp format:
28-JAN-11 03.25.11.000000000 PM
The above solution (changing the NLS preferences) did not work for me when I imported, but I finally got the following to work:
In the Import Wizard Column Definition screen, I entered "DD-MON-RR HH.MI.SSXFF AM" in the Format box, and it finally imported successfully. Unfortunately I have dozens of date fields and to my knowledge there is no way to systematically apply this format to all date fields so I had to do it manually....sigh. If anyone knows a better way I'd be happy to hear it!
Source: Stackoverflow.com