248

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?

Improve this question
3
  • I've hard trouble with this before. I can correct it for a session, but once I close SQL Developer and then restart it again, I lose the setting. So this solution I can provide is not permanent. Commented Nov 15, 2011 at 10:03
  • Can you add it an answer? @BrettWalker, it's better than nothing. Commented Nov 15, 2011 at 10:07
  • 5
    What a ridiculous default for a data type called "DATE" that actually stores precision to the second. This must have wasted a lot of people's time. Commented Nov 19, 2015 at 22:58

9 Answers 9

Reset to default
467

You can change this in preferences:

  1. From Oracle SQL Developer's menu go to: Tools > Preferences.
  2. From the Preferences dialog, select Database > NLS from the left panel.
  3. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field.
  4. Save and close the dialog, done!

Here is a screenshot:

Changing Date Format preferences in Oracle SQL Developer

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I'm using SQLDeveloper on linux and this setting doesn't work for me.. any other way?
No matter what format I put there, it is getting ignored. I see this getting generated in the sql: to_date('02-OCT-14','YYYY-MM-DD') so now my sql is just plain broken.
this format does not worked for me, maybe because of platform locale differences ... see my answer if this one does not work
where is this setting stored? in the DB or SQL Developer?
59

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'
Improve this answer

Comments

41

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

Settings Menu screenshot

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.

Improve this answer

Comments

8

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

Improve this answer

2 Comments

TL;DR: without 'XFF' at the end
In Oracle, a date does NOT have a fractional seconds part. A timestamp does. So, Oracle is trying to be helpful by not letting you specify something that won't work.
4

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
Improve this answer

1 Comment

It is important to note that the 'AM' format token is removed for the 24-hour format. If both the 'AM' and 'HH24' tokens are used, the 'AM' overrides the 'HH24'.
4

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

0

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!

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

jsyk , you profile pic reminds me om don corleone (wrong place for such comment)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.