HOW TO SET CUSTOM DATE FORMAT IN SQL USING NLS DATE FORMAT

We can execute the below script for displaying custom date format in sql. We can change the date format as per our requirement by changing the format string highlighted in bold.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

IF you want to set it by default while you login, you can write a trigger as below.

CREATE OR REPLACE TRIGGER DATE_TRIG
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
END;