Sql error 1861 22008 ora 01861 литерал не соответствует формату строки

ORA-01861 means that the format strings of DATE and CHAR between two operands are not comparable in date format, 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';

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';

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');

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';

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';

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 find 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 on 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 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_FORAMT 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';

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:UsersAdministrator>set 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.

Please note that, NLS_DATE_FORMAT can also be set as an environment variable, but it only affects NLS date format in RMAN, not for normal connections.

In some cases, odd performance issues may occur in your query, you may consider to 1) convert DATE to CHAR type, or/and 2) add some required indexes.

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. The Run-time Setting
2. The Permanent Setting

The Run-time Setting

C:UsersAdministrator>set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.country=US

The 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

Я пытаюсь вставить данные в существующую таблицу и получаю сообщение об ошибке.

  INSERT INTO Patient (PatientNo, PatientFirstName, PatientLastName, PatientStreetAddress, PatientTown, PatientCounty)  , PatientPostcode, DOB, Gender, PatientHomeTelephoneNumber, PatientMobileTelephoneNumber) ЗНАЧЕНИЯ (121, 'Miles', 'Malone', '64 Zoo Lane ',' Clapham ',' United Kingdom ',' SW4 9LP ',' 1989-12-09 '  , 'M', 02086950291, 07498635200);  

Ошибка:

  Ошибка, начинающаяся со строки: 1 в команде -INSERT INTO  Пациент (Номер пациента, Имя пациента, Имя пациента, Адрес улицы пациента, Город пациента, Страна пациентов, Почтовый индекс пациента, Дата рождения, Пол, Номер домашнего телефона пациента, Номер мобильного телефона пациента, Номер мобильного телефона пациента) ЗНАЧЕНИЯ (121, 'Miles', 'Malone', '64  , 'SW4 9LP', '1989-12-09', 'M', 02086950291,07498635200) Отчет об ошибке -SQL Ошибка: ORA-01861: литерал не соответствует строке формата 01861.  00000 - «литерал не соответствует строке формата» * Причина: входящие литералы должны иметь ту же длину, что и литералы в строке форматирования (за исключением начальных пробелов).  Если включен модификатор "FX", литерал должен точно соответствовать, без лишних пробелов. * Действие: Исправьте строку формата, чтобы она соответствовала литералу.  

Просто не уверен почему это продолжает происходить Я сейчас изучаю SQL, я буду благодарен за любую помощь!

Попробуйте заменить строковый литерал на date '1989-12- 09 ' с TO_DATE('1989-12-09','YYYY-MM-DD')

Формат, который вы используете для даты, не соответствует формату даты по умолчанию Oracle.

При установке Oracle Database по умолчанию ФОРМАТ ДАТЫ ПО УМОЛЧАНИЮ устанавливается равным дд-МММ-гггг .

Используйте функцию TO_DATE (dateStr, formatStr) или просто используйте модель формата даты dd-MMM-yyyy .

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'  

  ALTER SYSTEM SET NLS_DATE_FORMAT = 'YYYY-MM-DD'   

  $ db-> do ("ALTER SESSION SET NLS_DATE_FORMAT =  'ГГГГ-ММ-ДД');  

Вы также можете изменить ge формат даты для сеанса. Это полезно, например, в Perl DBI, где функция to_date () недоступна:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'  

Вы также можете навсегда установить nls_date_format по умолчанию:

  ALTER SYSTEM SET NLS_DATE_FORMAT = 'YYYY-MM-DD'   

В Perl DBI вы можете запускать эти команды с помощью метода do ():

  $ db-> do ("ALTER SESSION SET NLS_DATE_FORMAT =  'ГГГГ-ММ-ДД');  

http://www.dba-oracle.com/t_dbi_interface1.htmhttps://community.oracle.com/thread/682596? start = 15 & tstart = 0

  ORA-01861: литерал не соответствует строке формата  

  TO_DATE ('1989-12-09', 'YYYY-MM-DD')  

  ORA-01861: литерал не соответствует строке формата   

Это происходит из-за того, что вы пытались ввести литерал со строкой формата, но длина строки форматирования не была такой же, как длина литерала.

Вы можете решить эту проблему, выполнив следующие изменения.

  TO_DATE ('1989-12-09', 'YYYY-MM-DD')   

Как правило, если вы используете функцию TO_DATE, функцию TO_TIMESTAMP, функцию TO_CHAR и аналогичные функции, убедитесь, что предоставленный вами литерал соответствует строке формата, вы указали

Попробуйте использовать формат как dd-mon-yyyy, например, 08.02.2016 должен быть в формате ’08 -feb-2016 ‘.

Если вы указали правильный формат даты, он должен работать, пожалуйста, проверьте еще раз, если вы указали правильный формат даты во вставленных значениях

попробуйте сохранить дату как это гггг-мм-дд чч: мм: сс. ms, например: 1992-07-01 00: 00: 00.0, что сработало для меня

Импортировать внешний обмен