Search code examples
.netsqloracleoledbora-01858

Oracle not accepting dates before 2000

I'm doing an insert from a C# .NET app into an Oracle database. The query that is failing looks something like:

INSERT INTO staging (create_date) VALUES ('16-Nov-1999')

When I run it from SQL Navigator, it runs fine. Through .NET, the database throws:

ORA-01858: a non-numeric character was found where a numeric was expected

I ran a few test cases and confirmed that it's the year causing the exception. Anything after '31-Dec-1999' runs fine.

Solution

  • Better to use the TO_DATE function when submitting values that are to be stored as DATEs:

    INSERT INTO staging 
   (create_date) 
VALUES 
   (TO_DATE('16-Nov-1999', 'DD-MON-YYYY'))