How do I Change NLS Date Format in SQL Developer ?
Just like we said previously, NLS_DATE_FORMAT can be set at session, database or OS level. It depends on what you want to do.
GUI tools like SQL developer usually override NLS_DATE_FORMAT at any level once you connected to the database in it.
To customize NLS_DATE_FORMAT in SQL developer to improve your productivity in the working environment, we have 2 options.
Temporary NLS_DATE_FORMAT
We may change NLS_DATE_FORMAT at session time.
Current Format
Let's see the current format before changing it.
SQL Developer - Before Changing NLS_DATE_FORMAT
Alter Session Set NLS_DATE_FORMAT
We execute ALTER SESSION SET NLS_DATE_FORMAT statement in SQL editor to change it, just like we said in NLS_DATE_FORMAT, How and Why, as simple as that.
SQL Developer - NLS_DATE_FORMAT Changed
The datetime format has been changed, but the problem is that, we need to set the value every time we connect to the database. We might need a permanent solution.
Permanent NLS_DATE_FORMAT
To permanently set NLS_DATE_FORMAT in SQL developer, we need to change it in the "Preferences" of the tool.
Preferences
Here we navigate the function menu to "Preferences".
SQL Developer - Tools - Preferences
NLS
We go for "Database" -> "NLS" and enter datetime format in the field of "Date_Format".
SQL Developer - Preferences - Change NLS_DATE_FORMAT
Test
After you close the windows, the new datetime format takes effect immediately, so you don't have to logout / login.
Let's check the result.
SQL Developer - New NLS_DATE_FORMAT
We have customized NLS_DATE_FORMAT in SQL developer permanently.