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:
