change database date format from DD-MON-RR to DD/MM/YYYY

I'm trying to change the date format of my Oracle database permanently, so that it displays dates in the format "DD/MM/YYYY". Currently, the dates are showing up as "24-04-02", which is not the format I want.

I've already tried using the following commands:

  ALTER SYSTEM set NLS_DATE_FORMAT='DD/MM/YYYY';
  ALTER SYSTEM set NLS_DATE_FORMAT='DD/MM/YYYY' SCOPE=SPFILE;

However, these commands don't seem to have any effect on the date format.

I don't want to use TO_CHAR() because I'm not trying to change the format for a specific query, and I don't want to use ALTER SESSION because I don't want to have to change the format every time I start a new session.

What can I do to permanently change the date format of my Oracle database to "DD/MM/YYYY"?

In Oracle, a DATE is a binary data-type that consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS has these components and it is NEVER stored in any particular human readable format.

You CANNOT change how dates are stored.

If you want to see the binary representation of the date values then use:

SELECT DUMP(date_column) FROM table_name;

What you are seeing is how the client application (i.e. the program that you use to query the database) choses to display that binary information to you.

Some client applications (i.e. mainly those written by Oracle including SQL*Plus, SQL Developer and SQLcl) will set their default display format according to the NLS_DATE_FORMAT session parameter.

However, that is specific to those client applications. Internally, the database uses the NLS_DATE_FORMAT as the format model when implicitly casting from strings to dates and vice-versa. When a client application asks for a DATE data-type then the database sends the unformatted binary representation of the date and lets the client application handle how that is formatted.

Other client applications will have their own internal configuration and will use that to format dates when displaying them.

If you want to format a date in a specific way then:

1. Retrieve (unformatted) DATE data-types from the database and allow the client application to format it according to it's configuration (and, if required, update the settings of every client application); or
2. Don't display an (unformatted) DATE data-type and use TO_CHAR (or other functions) to format the date into a specific format.

As mentioned in other comments: don't waste your time! The database NLS_DATE_FORMAT is just the default format shown by the client in case nothing is defined at the client. However, in almost every case the client has set NLS_DATE_FORMAT, so the database NLS_DATE_FORMAT setting is ignored.

It would be quite difficult to configure a client to use the database default. For most of the tools (e.g. SQL Developer) it is even impossible unless you query the database NLS value and apply this value to an ALTER SESSION command.

See Setting NLS Parameters:

> Methods of Setting NLS Parameters and Their Priorities
>
> - 1: (highest) Explicitly set in SQL functions
> - 2: Set by an ALTER SESSION statement
> - 3: Set as an environment variable
> - 4: Specified in the initialization parameter file
> - 5: (lowest) Default value specified when the database was created

And this list is even not complete. For a Windows client you also have to add Registry NLS values from HKLM\SOFTWARE\ORACLE\KEY_OraClient.. and HKCU\SOFTWARE\ORACLE\KEY_OraClient..

For sqlplus you have to consider glogin.sql and login.sql, and so on.