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.

 3,650 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!