SQL

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

ORA-01861

ORA-01861 means that different format between two operands can not be compared. In this post, you will see some error patterns 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. Could it be a type mismatch issue? Let’s add TO_DATE function to convert the string into DATE.

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

Solutions to 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

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

2. Use TO_DATE

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

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

4 Persons

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

4. Change NLS_DATE_FORMAT

If you don’t want to modify your statement, even not 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

5. 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 SQL statements inside. You may found there’re many ORA-01861 complained about the date format. This is because the format of date strings used in the application does not match NLS settings in your environment.

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

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 Date format by country, there’re several languages use ‘YYYY-MM-DD’. For example, Swedish.

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

It connected without problems. Which means, Oracle database accepts the value of NLS_LANG.

SQL> set heading off;
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 date settings of the session follows NLS_LANG.

For windows platform, you can set NLS_LANG like the followings, it 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.

ORA-01861 in Oracle JDBC Driver

By default, Java uses the locale of OS as NLS settings, therefore NLS_LANG environment variable does not affect JDBC drivers in connecting to Oracle databases.

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

1. The 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

2. The Permanent Setting

Set JAVA_TOOL_OPTIONS Environment Variable
Set JAVA_TOOL_OPTIONS Environment Variable

Oracle JDBC driver will pick up JAVA_TOOL_OPTIONS Environment Variable and follow the instructions in it.

Further reading: How to Set NLS_DATE_FORMAT in RMAN

Leave a Reply

Your email address will not be published. Required fields are marked *