ORA-01861: literal does not match format string

ORA-01861

We’ve all been there before. You’re finally set to run a statement on a table that contains 20,000 data points and a dreaded ORA message appears. Before even thinking rationally, a sense of overwhelming urgency drifts over. What mistake could you have possibly made? How will you ever find it? What can you do?

Thankfully, Oracle databases are a little simpler to navigate and a certain subsection of Oracle errors will be so simple to resolve that as soon as you regain your composure, the error will be fixed in a split second. The ORA-01861 is one of those easy errors that’ll slow your heartbeat back down to a manageable pace. All you need is a quick run-through on Oracle formatting that we’ll provide for you right here.

The Problem

The ORA-01861 lists as resulting when “literal does not match format string”. If you’re not privy to these terms then this can seem a bit unclear. However, once you’re familiar with the two terms the rest falls right into place.

A literal in Oracle is a fixed, specific data point. For instance, in a list of names you may have literals like ‘BRAD’ or ‘CHERIE’, which are known as character literals. These will be written in single quotation marks for the purpose of identifying (remember this for later). You can also have numeric literals; perhaps the unique number of sick leave remaining on a table for each employee of your company expressed in total hours.

Finally, and often the source of the ORA-01861, are Datetime literals. These are in reference to calendar dates or timestamps and following a specific predetermined format string. There are four types of datetime literals: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The date time stamp uses the Gregorian calendar and follows the format ‘YYYY-MM-DD’. The TIMESTAMP adds to the date an expression of time that follows ‘HH:MM:SS.FFF’, where the F represents fractions of a second. A TIMESTAMP WITH TIME ZONE adds a ‘+HH:MM’, and a TIMESTAMP WITH LOCAL TIME ZONE simply stores the data in the time zone of the localized database. A full date/timestamp/time zone format string would appear as such:

TIMESTAMP ‘YYYY-MM-DD HH:MM:SS.FFF +HH:MM’

So judging by this, it should be pretty clear that often an ORA-01861 is triggered when a literal has been entered that fails to follow a format string. Perhaps you entered a date literal as ‘06/26/2015’ into a table with a date format string of ‘YYYY-MM-DD’. Naturally, the error would be thrown accordingly.

The Solution

The principle way to counter an ORA-01861 error is fairly simple. When the error is triggered, return to the line that Oracle indicated as the point of the error and edit the literal so that it matches the format string, keeping in mind that character literals require quotations and datetime literals require specific format strings. The following is an example of this solution in action.

Example of an ORA-01861 Error

SELECT TO_DATE (‘20140722’, ‘yyyy-mm-dd’)
FROM dual;
ERROR ORA-01861: literal does not match format string

Example of an ORA-01861 Solution

Above, the date literal excluded the hyphens between the year, month and day. A correct solution to this error would look as follows:

SELECT TO_DATE (‘2014-07-22’, ‘yyyy-mm-dd’)

FROM dual;

This will allow the statement to run smooth and error free. The more you remain cognizant of formatting, the less you’ll see this error.

Looking forward

As you can now see, the ORA-01861 error is about as straightforward as it gets. No coding, no deep exploration to the darkest depths of your tables to hunt down the problem. Just make sure to keep a consistent format and everything else should fall into place. With that said, there are always outlier cases with any software system. In the event that you run across such a situation, or perhaps just have a few general questions about more expansive topics like format strings, it can never hurt to contact a licensed Oracle consultant for more information.