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.