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.
Related Topics
How Would You Implement Sequences in Microsoft SQL Server
What Are the Use Cases for Selecting Char Over Varchar in SQL
Hierarchical Data in Linq - Options and Performance
How to Design a Database for User Defined Fields
What Does Sp_Reset_Connection Do
Rbar VS. Set Based Programming for SQL
Should I Design a Table with a Primary Key of Varchar or Int
If Exists Condition Not Working with Plsql
Join Multiple Tables with Active Records
Transpose Rows into Columns in Bigquery (Pivot Implementation)
Return a Value If No Record Is Found
How to Check Which Locks Are Held on a Table
SQL Error: Ora-01861: Literal Does Not Match Format String 01861
Correct Use of Transactions in SQL Server
Explode (Transpose) Multiple Columns in Spark SQL Table