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

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:

https://i.stack.imgur.com/b1Qsa.png

---

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' 

---

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

https://i.stack.imgur.com/qy5Um.jpg

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.

---

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

---

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 

---

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.

---

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

---

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 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!

---