0

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"?

Vergil_The_Killer
  • 1
  • 3
  • 1
    Your Oracle tooling _should_ be using your local computer's regional/locale settings (so if you're running Windows set to _English (United Kingdom)_ then it will use the `dd/MM/yyyy` format in results grids but `MM/dd/yyyy` if you're using Windows with _English (United States)_ - so setting `NLS_DATE_FORMAT` just changes the default-format for `TO_CHAR`. I think you're wasting your time trying to change something that you probably shouldn't, for no good reason. – Dai Jul 12 '23 at 13:31
  • @Dai i can assure you that i'm not wasting time here, i'm trying to change the format since it makes zero sense, reading this for example "05-05-05" will give anyone a seizure! – Vergil_The_Killer Jul 12 '23 at 13:35
  • 1
    Where/how are you seeing `05-05-05` though? What program/GUI/report-viewer? What do you see if you use a non-Oracle client tool to browse your table's data (such as an ODBC-standard client?) – Dai Jul 12 '23 at 13:36
  • @Dai a simple select query displays the dates like that, also i'm using SQL DEV to view/manage my database – Vergil_The_Killer Jul 12 '23 at 13:39
  • Have you seen this? https://stackoverflow.com/questions/8134493/how-can-i-set-a-custom-date-time-format-in-oracle-sql-developer – Dai Jul 12 '23 at 13:40
  • @Dai see, the thing is, i found that manual solution and tried it, it works, but i was tasked to find a script that will do it instead of having to do it manually. Don't know if it's possible tho – Vergil_The_Killer Jul 12 '23 at 13:41
  • 1
    If it *displays* something, then there's an external *consumer* that has connected to the database. According to the [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/NLS_DATE_FORMAT.html#GUID-FC23EEEE-AA9F-4B3C-8CBB-888C9C0CA27F): *"This initial value is **overridden by a client-side value** if the client uses the Oracle JDBC driver or if the client is OCI-based and the NLS_LANG client setting (environment variable) is defined. **The initialization parameter value is, therefore, usually ignored.**"* – astentx Jul 12 '23 at 14:01
  • This means that server setting will be available only in internal activities such as background jobs or similar. You need to use database level trigger to override client setting, but it would be *much* better in terms of supportability to be explicit when you deal with NLS dependent functions: implicit may be silently changed on one day. – astentx Jul 12 '23 at 14:05

2 Answers2

2

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.
MT0
  • 143,790
  • 11
  • 59
  • 117
1

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.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110