Ora-01861: Literal Does Not Match Format String

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')

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'

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);

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 error on char variable

Hm, period is a string? VARCHAR2 datatype column (or any other "CHAR")? Are you sure? Because, that Oracle error is related to DATE datatype.

Have a look at the following example:

SQL> desc emp
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                         NOT NULL NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE           --> HIREDATE is DATE datatype
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

What does my database return for any hiredate?

SQL> select hiredate from emp where rownum = 1;

HIREDATE
--------
17.12.80

Aha; OK, so let's try to select a row using the following date format (note that '1980-12-17' is a string, not a date!):

SQL> select empno, ename, hiredate from emp where hiredate = '1980-12-17';
select empno, ename, hiredate from emp where hiredate = '1980-12-17'
                                                        *
ERROR at line 1:
ORA-01861: literal does not match format string

Oh? Error just like yours. How about your date format?

SQL> select empno, ename, hiredate from emp where hiredate = '1980-12';
select empno, ename, hiredate from emp where hiredate = '1980-12'
                                                        *
ERROR at line 1:
ORA-01861: literal does not match format string

SQL>

Just the same, no difference. Oracle failed to implicitly "convert" strings I provided to a valid DATE value. So, what to do?

The worst thing is to keep providing strings and hoping that Oracle will guess the format:

SQL> select empno, ename, hiredate from emp where hiredate = '17.12.80';

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7369 SMITH      17.12.80

SQL>

A better option is to take control over it and provide DATE value, such as date literal (which always looks like this: date keyword, followed by date in yyyy-mm-dd format enclosed into single quotes):

SQL> select empno, ename, hiredate from emp where hiredate = date '1980-12-17';

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7369 SMITH      17.12.80

SQL>

This works.

Or, adjust NLS settings for current session (this is kind of a stupid format, just to show that it'll work regardless):

SQL> alter session set nls_date_format = 'mm/yyyy/dd';

Session altered.

SQL> select empno, ename, hiredate from emp where hiredate = '12/1980/17';

     EMPNO ENAME      HIREDATE
---------- ---------- ----------
      7369 SMITH      12/1980/17

SQL>

Therefore, I'd suggest you to a) check period column's datatype, b) take control over the process and compare dates to dates, not dates to strings.

P.S. Oh, yes - why does SQL Developer "work"? Its settings recognized format you provided.

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.

ORA-01861: literal does not match format string (I dont know why this is not working.)

You should explicitly cast the string to a date. Else, you rely on Oracle's ability to do that for you under the hood, which itself depends on whether the string matches the NLS_DATE_FORMAT of your database or session.

For this, you can do:

INSERT INTO ClientAppointment (Appointment_ID, Customer_ID, AppointmentDateTime, EmployeeID) 
VALUES (4472, 7753, date '2017-08-01', 788678);

This works because the format of your string corresponds to the ISO format for a date. In other situations, you would use to_date():

INSERT INTO ClientAppointment (Appointment_ID, Customer_ID, AppointmentDateTime, EmployeeID) 
VALUES (4472, 7753, to_date('2017-08-01', 'yyyy-mm-dd'), 788678);

Note: other values look like numbers, so I suspect they are. If so, you should treat them as such (ie not surround them with quotes).