Friday 23 September 2016

change date and time formats for database?

SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes

Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual;


SYSDATE

---------
23-SEP-16   

Here it is showing in format in DD-MONTH-YY format.If i 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

-----------------
23/SEPTEMBER/2016

If we want get the both(i.e date and time)

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

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------------
23/SEPTEMBER/2016 15:56:47  --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
--------------------------
23/SEPTEMBER/2016 03:57:18   --It is 12 hours format.

No comments:

Post a Comment