Which occurs when we define Date type column and passing strings in different form.
I wanted to overcome this issue when develop oracle DB script.
There are two solutions available.
Eg: - to_date(datestring, formatstring)
to_date('2010-01-05 14:05:29','yyyy-mm-dd hh24:mi:ss' )
- Or alter the evironment Date format
Eg:- alter system/session set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
But this is not good option. System level alter affects all other databases and session level alter also user need to do everytime.
So, create an trigger in your DB script. So it will be executed always when you start your database.
eg: create or replace trigger DATE_TRIGGER
after logon on database
begin
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
end;