How can I set a custom date time format in Oracle SQL Developer?

Question

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?

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. — Brett Walker, Nov 15 '11 at 10:03
Can you add it an answer? @BrettWalker, it's better than nothing. — ComfortablyNumb, Nov 15 '11 at 10:07
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. — WW., Nov 19 '15 at 22:58

score 455 · Accepted Answer · edited Jul 11 '13 at 14:21

455

You can change this in preferences:

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

Here is a screenshot:

Changing Date Format preferences in Oracle SQL Developer

edited Jul 11 '13 at 14:21

Rubens Mariuzzo

28,358
27
121
148

answered Nov 15 '11 at 10:12

ousoo

4,584
1
15
4

1

I'm using SQLDeveloper on linux and this setting doesn't work for me.. any other way? – Filipe Felisbino Dec 19 '12 at 16:10
4

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. – James Watkins Oct 29 '14 at 15:13
this format does not worked for me, maybe because of platform locale differences ... see my answer if this one does not work – Donatello Dec 05 '14 at 10:54
2

where is this setting stored? in the DB or SQL Developer? – Toolkit Jan 31 '17 at 12:08

hipsandy · Answer 2 · 2013-05-01T03:53:27.363

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'

score 39 · Answer 3 · answered Jul 03 '14 at 11:44

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.

score 8 · Answer 4 · answered May 18 '18 at 15:27

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

answered May 18 '18 at 15:27

Kapuśniak

81
1
1

3

TL;DR: **without 'XFF' at the end** – Robert Andrzejuk Sep 11 '18 at 12:22
1

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. – Roy Latham Sep 23 '19 at 19:00

score 4 · Answer 5 · edited May 09 '15 at 15:37

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

edited May 09 '15 at 15:37

Brett Walker

3,566
1
18
36

answered Mar 04 '13 at 23:06

Jay Dorsey

49
2

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'. – macawm May 27 '16 at 14:42

score 1 · Answer 6 · answered Jun 02 '17 at 06:39

1

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.

answered Jun 02 '17 at 06:39

Ashutosh Chopde

19
4

score 0 · Answer 7 · answered Jul 09 '13 at 15:09

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

answered Jul 09 '13 at 15:09

Sharan Rajendran

3,549
2
19
6

Brett Walker · Answer 8 · 2015-05-09T15:35:42.610

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.

edited May 09 '15 at 15:35

answered Nov 15 '11 at 10:14

Brett Walker

3,566
1
18
36

jsyk , you profile pic reminds me om don corleone (wrong place for such comment) – Jonathan DS Nov 24 '14 at 12:24

score -1 · Answer 9 · answered Jun 24 '14 at 20:05

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!

How can I set a custom date time format in Oracle SQL Developer?

9 Answers9

Linked

Related