Change Date and Time Formats In Oracle Database?

Change Date and Time Formats In Oracle Database?

Option 1. If We want to change this format to DD/MONTH/YYYY format.

SQL> alter session set nls_date_format=’DD/MONTH/YYYY’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

—————–

20/OCTOBER  /2017

 

Option 2. If we want get the both date and time

SQL> alter session set nls_date_format=’DD/MONTH/YYYY HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

————————–

20/OCTOBER  /2017 17:19:12 –It is 24 hours format.

 

SQL> alter session set nls_date_format=’DD/MONTH/YYYY HH:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

————————–

20/OCTOBER  /2017 05:19:43  –It is 12 hours format.