How do I fix not valid Month error in Oracle?

"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

How do I fix not valid months?

How do I resolve Ora 01843 Not a valid month error?

How do I change date format from YYYY

What is invalid number error in Oracle?