Oracle - Literal Does Not Match Format String Error

SQL Error: ORA-01861: literal does not match format string 01861

Try replacing the string literal for date '1989-12-09' with TO_DATE('1989-12-09','YYYY-MM-DD')

SQL Error: ORA-01861: literal does not match format string

Most likely, your NLS_DATE_FORMAT, the default date format for literals does not match your string. Never assume dates are formatted one way or another. use TO_DATE function to specify the format, so convert to :

Insert (... to_date('2001-04-15','YYYY-MM-DD')...

ORA-01861: literal does not match format string use oracle SQL

As date in your table is stored as a string, you will need to follows some basic steps:

• convert string to date
• add 3 months into converted date
• convert the date back to string which can be stored in your table.

Use the following query:

update psm_voucher 
   set expdt= to_char(add_months(to_date(expdt,'YYYYMMDD'),3), 'YYYYMMDD')
 where barcode='BBCV101670'

how to resolve Error :ORA-01861: literal does not match format string

You need to match the format of your date variable to the mask you are using:

to_date('20161104083815', 'YYYYMMDDHH24MISS')

Watch out that your date format start with the year - 2016, so your mask need to start with the year too -yyyy, and so on.

what should i do i keep getting ORA-01861: literal does not match format string ORA-06512: at SYS.DBMS_SQL, line 1721

You are simply missing the keyword DATE.

In your insert statement you have '2018-03-01'. This is a string literal, but the table column that is to receive this value is of data type DATE. This means Oracle must convert the string to a date. Now '2018-03-01' is not a date format that is commonly used in your country / region, so Oracle doesn't know how to safely convert it (e.g. how to decide which is day and which is month).

Use a date literal instead. For this we must use the keyword DATE followed by the format yyyy-mm-dd, which is what you are probably already using. Hence:

INSERT INTO task
  (task_id, task_descrip, task_starting_date, task_ending_date, number_of_employee)
  VALUES(1101, 'Initial interview', DATE '2018-03-01', DATE '2018-03-06', 3);

literal does not match format string oracle 11g

Obviously the format

TO_DATE('2003/05/03','DD-MON-YYYY')

does not match.

As already stated by mathguy MON means "Abbreviated name of month", not month number.

Apart from that the format should be one of the following (since I don't know whether you mean "3rd of May" or "5th of March")

TO_DATE('2003/05/03','YYYY/MM/DD')
TO_DATE('2003/05/03','YYYY/DD/MM')
TO_DATE('03-05-2003','DD-MM-YYYY')
TO_DATE('05-03-2003','DD-MM-YYYY')
TO_DATE('03-MAY-2003','DD-MON-YYYY')
TO_DATE('05-APR-2003','DD-MON-YYYY')

---

Related Topics