I’m writing this post so it can be found by googlers struggling with ORA-01861. Usually, ORA-01861 means that you missed something when using a date conversion function. But, here the other day I had created a table using some code generation tool, and I had accidentally declared a column with datatype DATE instead of NUMBER.
When you are using the wrong datatype in SQL, Oracle tries to convert it for you. If you try to insert letters into a DATE column, Oracle will return error message ORA-01858 - a non-numeric character was found where a numeric was expected, if you try to insert a number into it, you’ll get ORA-00932 - inconsistent datatypes: expected DATE got NUMBER. But if you insert a number in quotes, and the string is in the right format, Oracle may succeed. ORA-01861 comes when you submit a number in quotes, but is not a complete date.
So, if you see ORA-01861 and you haven’t used a conversion function, check your table definition.