ORA-01861: literal does not match format string error on char variable-CodePudding

We just got new computers at work and my SQL-code that I run in Python through cx_Oracle stopped working.

When I run the code it returns the ORA-01861 error saying that the literal does not match format string. I have read that this usually concerns dates, but in this case the error message refers to a char variable named period.

Basically my query says

where period = '2015-02'

which refers to a year and a month. The datatype in the database for period is char. Does someone recognize this error when dealing with a char variable?

I should say that when I run the same code in SQL Developer it works just fine.

Many thanks in advance.

CodePudding user response：

Hm, period is a string? VARCHAR2 datatype column (or any other "CHAR")? Are you sure? Because, that Oracle error is related to DATE datatype.

Have a look at the following example:

SQL> desc emp
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                         NOT NULL NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE           --> HIREDATE is DATE datatype
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

What does my database return for any hiredate?

SQL> select hiredate from emp where rownum = 1;

HIREDATE
--------
17.12.80

Aha; OK, so let's try to select a row using the following date format (note that '1980-12-17' is a string, not a date!):

SQL> select empno, ename, hiredate from emp where hiredate = '1980-12-17';
select empno, ename, hiredate from emp where hiredate = '1980-12-17'
                                                        *
ERROR at line 1:
ORA-01861: literal does not match format string

Oh? Error just like yours. How about your date format?

SQL> select empno, ename, hiredate from emp where hiredate = '1980-12';
select empno, ename, hiredate from emp where hiredate = '1980-12'
                                                        *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

Just the same, no difference. Oracle failed to implicitly "convert" strings I provided to a valid DATE value. So, what to do?

The worst thing is to keep providing strings and hoping that Oracle will guess the format:

SQL> select empno, ename, hiredate from emp where hiredate = '17.12.80';

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7369 SMITH      17.12.80

SQL>

A better option is to take control over it and provide DATE value, such as date literal (which always looks like this: date keyword, followed by date in yyyy-mm-dd format enclosed into single quotes):

SQL> select empno, ename, hiredate from emp where hiredate = date '1980-12-17';

     EMPNO ENAME      HIREDATE
---------- ---------- --------
      7369 SMITH      17.12.80

SQL>

This works.

Or, adjust NLS settings for current session (this is kind of a stupid format, just to show that it'll work regardless):

SQL> alter session set nls_date_format = 'mm/yyyy/dd';

Session altered.

SQL> select empno, ename, hiredate from emp where hiredate = '12/1980/17';

     EMPNO ENAME      HIREDATE
---------- ---------- ----------
      7369 SMITH      12/1980/17

SQL>

Therefore, I'd suggest you to a) check period column's datatype, b) take control over the process and compare dates to dates, not dates to strings.

P.S. Oh, yes - why does SQL Developer "work"? Its settings recognized format you provided.

CodePudding user response：

I solved it by setting

os.environ['NLS_LANG'] = 'swedish'

before my cx_Oracle connection.

I want to thank Littlefoot for helping me to boil this problem down to NLS settings and saved me from missing my deadline.