Oracle - literal does not match format string error
54,596
Solution 1
When you are inserting a string value to a date column, then you need to convert it to a date during the INSERT
using the to_date()
function. When using this function you will provide the format of the string.
to_date()
function format:
to_date( string1, [ format_mask ], [ nls_language ] )
So your query will be like this:
insert into CatalogueEntry
values
(
to_date('2001-12-10', 'yyyy-mm-dd'),
2,
14.99,
1,
0);
Solution 2
Try this SQL:
INSERT INTO CatalogueEntry
VALUES(to_date('2001-12-10','yyyy-mm-dd'), 2, 14.99, 1, 0);
Author by
AkshaiShah
Updated on July 15, 2022Comments
-
AkshaiShah almost 2 years
Possible Duplicate:
Simple Oracle query: literal does not match format stringI am getting the following error:
INSERT INTO CatalogueEntry VALUES('2001-12-10', 2, 14.99, 1, 0) ERROR at line 1: ORA-01861: literal does not match format string `
The first field is a
DATE
format.Any ideas?
Thanks.
-
a_horse_with_no_name over 11 yearsAnother alternative is to use an ANSI SQL literal:
date '2001-12-10'
-
Nick Krasnov over 11 years@a_horse_with_no_name
date
literal depends on NLS_DATE_FORMAT settings thus cannot be an alternative solution to OP's problem. -
a_horse_with_no_name over 11 years@NicholasKrasnov: The ANSI date literals are completely independent of any NLS or other environment settings (it has to be
YYYY-MM-DD
). So this is a valid alternative (less typing and works across a wide range of DBMS actually) -
Nick Krasnov over 11 years@a_horse_with_no_name Oh my, of course :). For some reason I mistakenly assumed opposite.
-
Taryn over 11 years@a_horse_with_no_name I was not aware that you can use an ANSI SQL Literal like that. Thanks for the info.