DATE format in Oracle SQL Developer

Question

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.

'1998-10-25' is a string, not a date. SQL Server does not support date literals, but Oracle does : DATE '1998-10-25'. — Thorsten Kettner

MT0 MT0 · Accepted Answer · 2020-11-02T16:24:26

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

DATE format in Oracle SQL Developer

2 Answers