Alter Session Set NLS_DATE_FORMAT in Oracle with Examples

Hi,

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

 

 

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

 

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

PL/SQL for real world – 8 features that you have to know

 3,254 views last month,  6 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 mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

blank

Alter System Flush Shared pool in Oracle

Hi, I will explain Alter System Flush Shared pool in Oracle in this post.  1,408 views …

Leave a Reply