NLS_DATE_FORMAT, How and Why

NLS_DATE_FORMAT defines the display format of a datetime string, which can be set at various levels to meet business requirements.

That is to say, NLS_DATE_FORMAT doesn't change the value you have, it only changes the display of datetime values.

Let's see the original format of a date time.


SQL> select sysdate from dual;



SYSDATE

---------

09-MAR-23

As you can see, we can hardly know the precise time from the above format. To make the date time display in an easily readable format, you can set NLS_DATE_FORMAT at various level.

Session Level

We use ALTER SESSION SET NLS_DATE_FORMAT statement.

Instance Level

We use ALTER SYSTEM SET NLS_DATE_FORMAT statement.

OS Level

We use both export NLS_LANG and export NLS_DATE_FORMAT command to make it.

Please note that, there's no way to change NLS_DATE_FORMAT at database level. Please check the post for more information: NLS_DATABASE_PARAMETERS, How and Why.

Alter Session Set NLS_DATE_FORMAT

An ALTER SESSION SET NLS_DATE_FORMAT can keep all date time in this format during the session. For example:


SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';



Session altered.



SQL> select sysdate from dual;



SYSDATE

-------------------

2023-03-09 00:54:14

That's better. Moreover, the above datetime format is broadly used in RDBMS, such as MySQL's default datetime format.

Datetime Format Elements

In the above example, we used the following elements.

YYYY

4-digit year.

2-digit month of year, from 01 to 12.

2-digit day of month, from 01 to 31.

HH24

2-digit hour of day. 24 means that it uses 24-hour notation ranging from 00 to 23.

2-digit minute of hour, from 00 to 59.

2-digit second of minute, from 00 to 59.

To delicately format your datetime for various situations, you need to know more format elements for datetime.

DateTime Comparison

Usually, we use TO_DATE function to convert our strings for comparing datetime values. However, we can also use a well-formatted string which conforms to current NLS_DATE_FORMAT to compare datetime column.

Let's see some examples.

Using TO_DATE

No matter what NLS_DATE_FORMAT currently is, we can always use TO_DATE to format the string at run-time.


SQL> select 'TRUE' result from dual where sysdate > to_date('2023-03-09 00:54:14', 'YYYY-MM-DD HH24:MI:SS');



RESU

----

TRUE

It looks a little uneasy to read. For long SQL statements, it'll get worse.

Using Strings

As long as you follow NLS_DATE_FORMAT, you can directly use a string to compare with a datetime.

In WHERE clause, we can naturally compare them.


SQL> select 'TRUE' result from dual where sysdate > '2023-03-09 00:54:14';



RESU

----

TRUE

In SELECT clause, we should use CASE WHEN to compare two datetimes.


SQL> select case when sysdate > '2023-03-09 00:54:14' then 'TRUE' else 'FALSE' end result from dual;



RESUL

-----

TRUE

These comparisons look pretty straightforward.

Alter System Set NLS_DATE_FORMAT

For providing a globally used datetime format, you can make it take effect at the instance level by setting the parameter.

Let's check the current setting.


SQL> show parameter nls_date_format



NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_date_format                      string

There's nothing, which means that the display format is determined by their own sessions.

Set Parameter

To make the format globally use, we may set the parameter within SPFILE scope by ALTER SYSTEM SET NLS_DATE_FORMAT.


SQL> alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;



System altered.

Restart Database

Then we perform a database restart to make the new value take effect.


SQL> shutdown immediate;

...

SQL> startup

...

SQL> show parameter nls_date_format



NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_date_format                      string      YYYY-MM-DD HH24:MI:SS

From now on, every session is affected by the format, except that they change it at their own session time, just like we said in Section A above.

Export NLS_DATE_FORMAT

For specific OS user, you may use both NLS_DATE_FORMAT and NLS_LANG environment variables to set the datetime format in shell. Oracle will pick up those variables and use them.

In other words, NLS_DATE_FORMAT is not only a database parameter but also an environment variable, depending on how you use it.

Let's see how we do it on Linux, Windows and for RMAN.

Linux

Setting NLS_LANG makes database know what NLS_DATE_FORMAT it should use in our working environment, but the format may not meet our requirements.


[oracle@test ~]$ export NLS_LANG=AMERICAN

NLS_LANG usually consists of 3 components, here we use only the first component.

Then we override the datetime format to meet our requirements.


[oracle@test ~]$ export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

A permanent solution can be done by setting those environment variables in the user's profile.


[oracle@test ~]$ vi ~/.bash_profile

...

export NLS_LANG=AMERICAN

export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

...

[oracle@test ~]$ . ~/.bash_profile

Windows

Same as the above, we set both environment variables.


C:\Users\alex>set NLS_LANG=AMERICAN


C:\Users\alex>set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

Please don't use double or single quotes to set the variable on Windows.

Alternatively, you can add both in Environment Variables Editor permanently.

RMAN

Setting only NLS_DATE_FORMAT is enough for RMAN users to display the correct date time format. NLS_LANG is not necessary.

For 12c and later releases, we can set NLS_DATE_FORMAT at session time in RMAN.

In summary, the rules of override precedence are:

Setting NLS_DATE_FORMAT at database-level provides a default datetime format for users.
Setting NLS_LANG at OS-level implicitly changes NLS_DATE_FORMAT at database-level.
Setting NLS_DATE_FORMAT at OS-level overrides the datetime format that NLS_LANG provides.
Setting NLS_DATE_FORMAT at session-level overrides the datetime format, no matter what it originally comes from. The decision has been make.

GUI database tools usually have their own default NLS_DATE_FORMAT in their preferences, they can be changed. Please check the following links to know.

To know all NLS settings in your session, you may query NLS_SESSION_PARAMETERS for sure.