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.
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.
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".
NLS
We go for "Database" -> "NLS" and enter datetime format in the field of "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.
We have customized NLS_DATE_FORMAT in SQL developer permanently.