How can I set a custom date time format in Oracle

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?

Answer 1

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

Answer 2

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!

Answer 3

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'