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

Question

I am trying to insert data into an existing table and keep receiving an error.

INSERT INTO Patient  
(
  PatientNo,
  PatientFirstName,
  PatientLastName,
  PatientStreetAddress,
  PatientTown,
  PatientCounty,
  PatientPostcode,
  DOB,
  Gender,
  PatientHomeTelephoneNumber,
  PatientMobileTelephoneNumber
)
VALUES 
(
  121, 
  'Miles', 
  'Malone', 
  '64 Zoo Lane', 
  'Clapham', 
  'United Kingdom',
  'SW4 9LP',
  '1989-12-09',
  'M',
  02086950291,
  07498635200
);

Error:

Error starting at line : 1 in command -
INSERT INTO Patient (PatientNo,PatientFirstName,PatientLastName,PatientStreetAddress,PatientTown,PatientCounty,PatientPostcode,DOB,Gender,PatientHomeTelephoneNumber,PatientMobileTelephoneNumber)
VALUES (121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom','SW4 9LP','1989-12-09','M',02086950291,07498635200)
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

Just not sure why this keeps happening I am learning SQL at the moment, any help will be greatly appreciated!

can we see your table structure? I suspect it is the phone numbers that are `VARCHAR2` or something and you're inserting them as `NUMBER` — CodeBird, Mar 20 '14 at 19:16
@MikeBurton CREATE TABLE Patient ( PatientNo CHAR(5) NOT NULL PRIMARY KEY, InvoiceNo CHAR(5) NOT NULL, PatientFirstName VARCHAR2(20) NOT NULL, PatientLastName VARCHAR2(20) NOT NULL, PatientStreetAddress VARCHAR2(40) NOT NULL, PatientTown VARCHAR2(20), PatientCounty VARCHAR2(20), PatientPostcode VARCHAR2(8) NOT NULL, DOB DATE NOT NULL, Gender CHAR(1) CONSTRAINT pat_g_nn NOT NULL, PatientHomeTelephoneNumber VARCHAR2(11) CONSTRAINT pat_phtn_nn ); — LizzyPooh, Mar 20 '14 at 19:32
I bet it's the `DOB` column. If that's the case, try using the `TO_DATE` function to parse the date string `'1989-12-09'` properly. — mustaccio, Mar 20 '14 at 19:32
`PatientHomeTelephoneNumber` is varchar2, you're inserting it as `NUMBER`, it should be surrounded by `''` and I don't see `PatientMobileTelephoneNumber` in your create statement. — CodeBird, Mar 20 '14 at 19:34

score 208 · Accepted Answer · answered Mar 20 '14 at 19:33

208

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

answered Mar 20 '14 at 19:33

mustaccio

18,234
16
48
57

12

Or since it's in the right format, you could use a date literal as `date '1989-12-09'` instead of using `to_date()`. – Alex Poole Mar 20 '14 at 19:36
1

Thanks guys, one more question, if you was to do time eg. appointment time, would you have to put something in front of the time if its formatted as '17:43:09'?? – LizzyPooh Mar 20 '14 at 19:38
@alexpoole is it the same way for a time aswell ?? – LizzyPooh Mar 20 '14 at 20:04
There's no time data type in Oracle, it'll still be `date` and I think you'll need to include the date part of it as well. – mustaccio Mar 20 '14 at 20:13
2

@LizzyPooh - you can store an appointment date/time as a `DATE`, populating it with `to_date('2014-03-21 09:15:00', 'YYYY-MM-DD HH24:MI:SS')`, or `timestamp '2014-03-21 09:15:00.0'` - [read more](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#sthref364). – Alex Poole Mar 20 '14 at 21:11
In general this error happens when we provide a value different to format we have defined in to_date – Kavindu Dodanduwa Feb 25 '16 at 09:28
@AlexPoole How do you know it's the right format as both month and day are not greater than 12? – Piotr Dobrogost Aug 23 '16 at 13:22
@PiotrDobrogost - that's the [ISO date format](https://en.wikipedia.org/wiki/ISO_8601); so it's a small assumption but using YYYY-DD-MM would be non-standard and cause great confusion. One of the great advantages of the ISO format is that it is unambiguous. Also the format mask in the answer is YYYY-MM-DD... – Alex Poole Aug 23 '16 at 13:26
@AlexPoole You should check date format using `SELECT * FROM V$NLS_PARAMETERS` – Alex78191 Dec 28 '18 at 10:28
@Alex78191 - the point of the answer using an explicit format mask (or of using a date literal) is that you don't rely on the NLS settings - which the developer can't assume will be the same for whoever runs the code. The original error means the OP's NLS settings didn't match the (apparently ISO) format in the insert anyway. Other answers refer to changing the supplied literal to match NLS settings, but it's still not a good idea to rely on those. – Alex Poole Dec 28 '18 at 10:36

score 16 · Answer 2 · edited Sep 04 '19 at 21:44

16

The format you use for the date doesn't match to Oracle's default date format.

A default installation of Oracle Database sets the DEFAULT DATE FORMAT to dd-MMM-yyyy.

Either use the function TO_DATE(dateStr, formatStr) or simply use dd-MMM-yyyy date format model.

edited Sep 04 '19 at 21:44

Ahmet

908
1
17
26

answered May 14 '15 at 07:44

Mitz

561
8
21

score 9 · Answer 3 · answered Dec 13 '16 at 16:29

You can also change the date format for the session. This is useful, for example, in Perl DBI, where the to_date() function is not available:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'

You can permanently set the default nls_date_format as well:

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD'

In Perl DBI you can run these commands with the do() method:

$db->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD');

http://www.dba-oracle.com/t_dbi_interface1.htm https://community.oracle.com/thread/682596?start=15&tstart=0

score 5 · Answer 4 · answered Jun 21 '18 at 05:56

ORA-01861: literal does not match format string

This happens because you have tried to enter a literal with a format string, but the length of the format string was not the same length as the literal.

You can overcome this issue by carrying out following alteration.

TO_DATE('1989-12-09','YYYY-MM-DD')

As a general rule, if you are using the TO_DATE function, TO_TIMESTAMP function, TO_CHAR function, and similar functions, make sure that the literal that you provide matches the format string that you've specified

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

4 Answers4

Linked

Related