Oracle date format format ends before converting the entire input string

Question

My table has two DATE format attributes, however, when I try to insert a value, it causes an error: the date format ends before converting the entire input string. Here is my code:

insert into visit values(123456, '19-JUN-13', '13-AUG-13 12:56 AM'); 

I think the problem is with 12:56 , but the Oracle documentation says that date means date and time.

Answer 1

Perhaps you should check NLS_DATE_FORMAT and use a date string that matches the format. Or you can use the to_date function in an INSERT , as shown below:

 insert into visit values(123456, to_date('19-JUN-13', 'dd-mon-yy'), to_date('13-AUG-13 12:56 AM', 'dd-mon-yyyy hh:mi AM')); 

In addition, Oracle DATE stores the date and time .

Answer 2

you need to change the session

you can try before pasting

  sql : alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' 

Answer 3

What you are trying to insert is not a date, I think, but a string. You need to use the to_date() function, for example:

 insert into table t1 (id, date_field) values (1, to_date('20.06.2013', 'dd.mm.yyyy')); 

Answer 4

I had this error today and found that it was an incorrectly formatted year ...

 select * from es_timeexpense where parsedate > to_date('12/3/2018', 'MM/dd/yyy') 

Please note that there are only three years in a year. Must be 4.

Double check your format.