Display Date and Time for DATE Datatype.

Session Level

To display time with date for DATE datatype in SQLPLUS/SQL Developer/Toad etc. we can set “NLS_DATE_FORMAT” at session level using below command.

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’;

SQL>--Before altering session
SQL> select sysdate from dual;
SYSDATE
---------
31-JUL-23

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> select sysdate from dual;
SYSDATE
--------------------
31-JUL-2023 12:41:59

SQL> select sysdate + 1; /****Note - 23c Feature without from clause****/
SYSDATE+1
--------------------
01-AUG-2023 12:42:24
SQL>

Permanent Settings to Display Date Format in SQL Developer

In SQL developer we set at session level or make change in settings and display required format.

Go to Tools -> Preferences -> Database -> NLS -> Date Format (Change to Required Date Format).

Before setting

After Setting

SQL Developer Sample

Leave a Comment