After Oracle DB installation, db automatically set default date format. When we want to insert row to our tables, we can get error because of date format onto that column. Because our table’s date format and system’s date format are not suit each other. When we get this error we can change the date format for session or system. With the queries mentioned below, we can see the system date formats. We have to update ‘NLS_DATE_FORMAT’.
select * from nls_session_parameters
select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
With the Alter queries we can alter the date format. If you run ‘alter session’ query, it will alter the row for only that session. When user logout or session going down for any reason, system date format will be rollback again, system format will be turn back to default format. For permanent update you have to run ‘alter system’ query.
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss' ; alter system set nls_date_format='DD-MON-YYYY hh24:mi:ss' ;
If you use SqlPlus*, run these queries.
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss' scope=spfile; alter system set nls_date_format='DD-MON-YYYY hh24:mi:ss' scope=spfile;
If you dont want to alter system date format you can use that trigger. You can create a trigger which will alter the date format for every time user logon to database. Trigger will be alter session every time user logon to database. So, user can insert or update their tables without change default system format.
CREATE OR REPLACE TRIGGER CHANGE_DATE_FORMAT
AFTER LOGON ON DATABASE
BEGIN
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
END
