Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

How do I change date format from YYYY-MM-DD in Oracle?

Just use: select to_date(date_value, 'yyyy-mm-dd') as date_value from table; To convert to a date. That's it!

What is the date format in Oracle SQL Developer?

Oracle date format The standard date format for input and output is DD-MON-YY e.g., 01-JAN-17 which is controlled by the value of the NLS_DATE_FORMAT parameter.

How do I change the default date format in Oracle?

Oracle Database Dates Setting the Default Date Format Model SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT'; You can set this value within your current session using: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';


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


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

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.


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