CODEWITHSUNDEEP
sqloracle-database
Davide
Davide

Reputation: 57

DATE format in Oracle SQL Developer

Just a small issue with some coding within Oracle SQL Developer. This is my first time away from SQL Server and i'm already confused as to what I have got wrong.

I'm just trying to insert some data into a table but i'm reaching an error that states;
Error report - ORA-01861: literal does not match format string

I have the date field in my table set up as data type DATE. All I am trying to insert is some basic information;
INSERT INTO BasicUsers VALUES(2125, 'Dave', 'Forest', 'John', '1998-10-25', 30.54);

I have done some research on the internet and there is nothing solid for me to understand. I have tried altering the date format to YYYY-MON-DD so 1998-Oct-25 but that isn't working either.

Can anyone explain what I have done wrong here and how I can rectify this issue.
Thank you.

Upvotes: 0

Views: 2017

Answers (2)

MT0
MT0

Reputation: 168671

'1998-10-25' is not a DATE data type; it is a string literal.

Instead, use a DATE literal:

INSERT INTO BasicUsers  VALUES(2125, 'Dave', 'Forest', 'John', DATE '1998-10-25', 30.54);

Or use the TO_DATE function with a format model to explicitly convert from a string to a DATE:

INSERT INTO BasicUsers
  VALUES(2125, 'Dave', 'Forest', 'John', TO_DATE( '1998-10-25', 'YYYY-MM-DD' ), 30.54);

Don't rely on implicit conversions from a string to a DATE as Oracle will implicitly try to convert the date to a string using the NLS_DATE_FORMAT session parameter. Your query is effectively:

INSERT INTO BasicUsers
  VALUES(2125, 'Dave', 'Forest', 'John',
  TO_DATE(
    '1998-10-25',
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
  ),
  30.54);

If the session parameter does not match then your query will raise an exception. Since the user can change their session parameters at ANY time then you can never rely on this format to be consistent between users or even user's sessions so you should always be explicit about the conversions that you are performing.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Use the DATE prefix:

INSERT INTO BasicUsers 
    VALUES (2125, 'Dave', 'Forest', 'John', DATE '1998-10-25', 30.54)

I would also advise you to include explicit column names for the insert. For instance, the values could be defined in a different order from how you are providing the values.

Upvotes: 2

Related Questions