ORA-01843: not a valid month - NLS_DATE_FORMAT

"ORA-01843: not a valid month" is a very common exception which we all might have faced while working with Dates in Oracle Database. ORA-01843 occurs due to implicit date conversion which is Basics yet Important feature of Oracle Database. With this blog I am trying to explain the reason of ORA-01843 and how we can avoid it.

Following is the EMP table in SCOTT schema.

SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81    5000.15                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81     2850.3                    30
      7782 CLARK      MANAGER         7839 09-JUN-81    2450.45                    10
      7566 JONES      MANAGER         7839 02-APR-81     2975.5                    20
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7902 FORD       ANALYST         7566 03-DEC-81     3000.6                    20
      7369 SMITH      CLERK           7902 17-DEC-80     800.75                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81     1600.9        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250.05       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500.95          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20
      7900 JAMES      CLERK           7698 03-DEC-81     950.99                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

In my database, If I try to compare the HIREDATE with a string in either MM/DD/YYYY or DD/MM/YYYY format, it fails with ORA-01843

SQL> select * from emp where hiredate > '31/12/1985';
select * from emp where hiredate > '31/12/1985'
                                   *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select * from emp where hiredate > '12/31/1985';
select * from emp where hiredate > '12/31/1985'
                                   *
ERROR at line 1:
ORA-01843: not a valid month

In above examples Oracle Database tried implicit date conversion, which is only successful when String values are in default date format. We can find the default date format of our Oracle database using NLS_DATE_FORMAT -

SQL> select sys_context ('USERENV', 'NLS_DATE_FORMAT') from dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
---------------------------------------------------
DD-MON-RR

Now if we try to compare the HIREDATE with a string in DD-MON-RR (default) format, it should work.

SQL> select * from emp where hiredate > '31-Dec-85';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20

We should remember that Default date format might get changed, so we should be never dependent on implicit date conversion, and convert very string with date value using TO_DATE function.

SQL> select * from emp where hiredate > to_date('12/31/1985','mm/dd/yyyy');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20

At last, I wanted to add, we can also change NLS_DATE_FORMAT at session level -

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

SQL> select * from emp where hiredate > '12/31/1985';

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 04/19/1987    3000.55                    20
      7876 ADAMS      CLERK           7788 05/23/1987    1100.01                    20

As you might have noticed, by setting NLS_DATE_FORMAT, we can not only control the implicit conversion while running our SQL, but the output of DATE values is also as per NLS_DATE_FORMAT. I use above trick while debugging only, but when I am writing database code or sql, I always use TO_DATE

I hope you have enjoyed reading this article. Please do post your comments.

Related Posts -
- ORA-01830 date format picture ends before converting entire input string
- Why do I Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
- Playing With Truncate and Date in Oracle Database
- Oracle: Some Important Date Queries
- Oracle SQL: Date Difference in Days, Months and Year