Alter Session Set NLS_DATE_FORMAT in Oracle with Examples

I will explain Alter Session Set NLS_DATE_FORMAT in Oracle with Examples in this post.

Alter Session Set NLS_DATE_FORMAT

Oracle nls_date_format environmental variable is used to display the Date in different formats.

Firstly check sysdate from dual as follows.

SQL> select sysdate from dual;

SYSDATE
---------
14-MAY-20

Set NLS_DATE_FORMAT

Now change NLS_DATE_FORMAT = ‘YYYY MM DD’ as follows.

SQL> 
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
2020 05 14

SQL>

And change NLS_DATE_FORMAT = ‘HH24:MI:SS’ to display the Time with hour, minutes and seconds.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------
15:22:36

Change it as NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’ to show Year,month,day and hour minute second.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> 
SQL> select sysdate from dual;

SYSDATE
-------------------
2020-05-14 15:23:08

You can display it with – and / characters as follows.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
2020-05-14



SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
14/05/2020

SQL> 
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
14/05/2020 15:24:51

SQL>

If you want to learn more details about PL/SQL, Read the following posts.

PL/SQL Datetime Functions

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.