9

Possible Duplicate:
Simple Oracle query: literal does not match format string

I 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.

Community
  • 1
  • 1
AkshaiShah
  • 5,739
  • 11
  • 37
  • 45

2 Answers2

23

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

See SQL Fiddle with demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 3
    Another alternative is to use an ANSI SQL literal: `date '2001-12-10'` –  Nov 22 '12 at 18:56
  • @a_horse_with_no_name `date` literal depends on NLS_DATE_FORMAT settings thus cannot be an alternative solution to OP's problem. – Nick Krasnov Nov 22 '12 at 19:16
  • 2
    @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) –  Nov 22 '12 at 20:12
  • @a_horse_with_no_name Oh my, of course :). For some reason I mistakenly assumed opposite. – Nick Krasnov Nov 22 '12 at 20:53
  • @a_horse_with_no_name I was not aware that you can use an ANSI SQL Literal like that. Thanks for the info. – Taryn Nov 23 '12 at 10:37
1

Try this SQL:

INSERT INTO CatalogueEntry 
              VALUES(to_date('2001-12-10','yyyy-mm-dd'), 2, 14.99, 1, 0);
gmauch
  • 1,316
  • 4
  • 25
  • 39
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72