How to Resolve ORA-01861: literal does not match format string

ORA-01861

ORA-01861 means that the date format between two operands cannot be comparable. Usually, it happens when comparing DATE with CHAR. Therefore, we should make them match with each other in order to avoid ORA-01861.

In this post, you will see some error patterns of ORA-01861 and their respective solutions.

The following statement looks like no problem.

SQL> conn hr/hr
Connected.
SQL> set heading off;
SQL> select count(*) || ' Persons' from employees where hire_date > '2008-03-01';
select count(*) || ' Persons' from employees where hire_date > '2008-03-01'
                                                               *
ERROR at line 1:
ORA-01861: literal does not match format string

But it threw ORA-01861 eventually. Let's try to add TO_DATE function to convert the string into DATE value.

SQL> select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01');
select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01')
                                                                       *
ERROR at line 1:
ORA-01861: literal does not match format string

Even though they should have been comparable, their different formats stopped doing this. That is, the root cause is datetime format mismatch, not data type mismatch.

Here we talked two error patterns of ORA-01861 in this post:

1. Date format mismatch issues.
2. JDBC driver's problem specific for ORA-01861.

Date Format Mismatch ORA-01861

Converting the date string into a DATE is not working. There still have format mismatching problem. Now, let's see what date format does the database accept?

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE';

AMERICAN

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

DD-MON-RR

There're 5 ways that can solve ORA-01861 and make formats between date and string match each other.

1. Conform to NLS_DATE_FORMAT
2. Use TO_DATE
3. Use TO_CHAR
4. Change NLS_DATE_FORMAT at Session-Time
5. Set NLS_LANG

Conform to NLS_DATE_FORMAT

As we can see, our date string '2008-03-01' does not match the current date format 'DD-MON-RR'. Let's conform to the current date format by converting the date string from '2008-03-01' into '01-MAR-08'.

SQL> select count(*) || ' Persons' from employees where hire_date > '01-MAR-08';

4 Persons

Please note that, you don't have to use TO_DATE function to convert the string into a date value, an implicit conversion will be processed.

Use TO_DATE

The statement now is working, but sometimes you may still want to use the original date string. You can format the date string by TO_DATE function.

SQL> select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01', 'YYYY-MM-DD');

4 Persons

Use TO_CHAR

On the other hand, you can also convert DATE into string by TO_CHAR in order to compare the date string.

SQL> select count(*) || ' Persons' from employees where to_char(hire_date, 'YYYY-MM-DD') > '2008-03-01';

4 Persons

You might have some performance issue by applying this solution if the table is really big. A function-based index might be required for the computed values. In this case, it's TO_CHAR(HIRE_DATE, 'YYYY-MM-DD').

Change NLS_DATE_FORMAT at Session-Time

If you don't want to modify your statement, not even a tiny bit, you can set NLS_DATE_FORMAT at session-level to align with your date string format.

SQL> alter session set nls_date_format = 'YYYY-MM-DD';

Session altered.

SQL> select count(*) || ' Persons' from employees where hire_date > '2008-03-01';

4 Persons

SQL> exit

Set NLS_LANG

Here comes a more advanced topic about NLS_DATE_FORMAT. Sometimes, you can not change the application that you're using in your environment, not even the SQL statements inside.

So every time you run it, there're many ORA-01861 complained about the date format. This is because the format of date strings used by the application does not match NLS settings in your environment.

In such moment, the only thing you can do is set environment variable NLS_LANG or NLS_DATE_FORMAT at OS level to make date format of every session running on the platform comply with the application so as to prevent ORA-01861.

Use Only NLS_LANG

In our case, the format of our date string '2008-03-01' is 'YYYY-MM-DD', so what should we set in NLS_LANG? According to NLS_TERRITORY to NLS_DATE_FORMAT Mapping Table, there's only few territories use 'YYYY-MM-DD' (or 'RRRR-MM-DD'), one of which is SWEDEN.

In the following setting, we set NLS_LANG as SWEDISH language which subsequently changed NLS_TERRITORY into SWEDEN.

[oracle@test ~]$ export NLS_LANG=Swedish

Please note that, NLS_DATE_FORMAT is literally derived from NLS_TERRITORY, not from NLS_DATE_LANGUAGE.

[oracle@test ~]$ sqlplus /nolog
...
SQL> conn hr/hr
Connected.

It connected without ORA-12705 which means that Oracle database accepted the value of NLS_LANG.

SQL> set heading off;
SQL> select value from v$nls_parameters where parameter = 'NLS_TERRITORY';

SWEDEN

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE';

SWEDISH

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

YYYY-MM-DD

SQL> select count(*) || ' Persons' from employees where hire_date > '2008-03-01';

4 Persons

As you can see, NLS settings of the session follows NLS_LANG. The best thing is that we don't need to modify the statement, we just use NLS_LANG to align with its format string.

For windows platform, you can set NLS_LANG like the followings, it also works.

C:\Users\Administrator>set NLS_LANG=Swedish

C:\Users\Administrator>echo %NLS_LANG%
Swedish

Although NLS_LANG has a fixed format including language, territory and character set that I have talked about it in another post, it can accept only languages or territory.

Use Both NLS_LANG and NLS_DATE_FORMAT

If you also set NLS_DATE_FORMAT in OS shell, it overrides the datetime format derived from NLS_LANG. You may follow the link to know it.

JDBC Driver Problem ORA-01861

Here comes the most advanced topic about ORA-01861 in this post. By default, Java uses the locale of OS as NLS settings, therefore NLS_LANG environment variable does not affect Oracle JDBC drivers in connecting to Oracle databases.

Changing the locale of OS maybe a solution to ORA-01861, but seriously, it would be a big issue to other applications or other users. A lower-cost solution is to add another environment variable called JAVA_TOOL_OPTIONS.

There're 2 ways to set JAVA_TOOL_OPTIONS so as to solve ORA-01861 problem from JDBC.

1. Run-time Setting
2. Permanent Setting

Run-time Setting

C:\Users\Administrator>set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.country=US

C:\Users\Administrator>echo %JAVA_TOOL_OPTIONS%
-Duser.language=en -Duser.country=US

Permanent Setting

Oracle JDBC driver will pick up JAVA_TOOL_OPTIONS Environment Variable and follow the instructions in it. That's how we solve ORA-01861 for Oracle JDBC driver.

Further reading: How to Set NLS_DATE_FORMAT in RMAN