CODEWITHSUNDEEP
oracle-databasedatetimeoracle10g
Rebooting
Rebooting

Reputation: 2938

date and time oracle error

I am trying to show the date and time in oracle where I have altered the date and time formats as:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';
insert into train 
  values(18103,'2011/apr/30 21:00:00','2011/apr/31 21:00:00','abl','ndl');

But I am getting the error as ORA-01861: literal does not match format string

I have also tried entering the date and time as, 2011/04/30 21:00:00 but still it returns the same error! help!

Upvotes: 0

Views: 255

Answers (2)

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

Your nls_date_format does not match the format of the date string you insert. It should be:

SQL>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MON/DD HH24:MI:SS';

session SET altered.

SQL>insert into dat 
    values('2011/apr/30 21:00:00');

1 rows inserted.

SQL>select d from dat;

D                    
----------------------
2011/APR/30 21:00:00

Upvotes: 2

DazzaL
DazzaL

Reputation: 21993

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

these have to be combined. eg

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'

also on your insert be sure to TO_DATE eg to_date('2011/apr/31 21:00:00', 'yyyy/mon/dd hh24:mi:ss')

Upvotes: 0

Related Questions