Error ORA-01843 occurs when the user specifies a date that is not a valid calendar month. Valid months are: January, February, March, April, May, June, July, August, September, October, November, and December for format code MONTH. For the MON format code, valid month values are: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec.

To correct error ORA-01843, you must find the error and enter a valid month value in its correct form.

There are mainly two reasons the user sees this error. The first reason is that the incorrect NLS_DATE_FORMAT is being used. This will often occur when you are working with data from the United States where the commonly used syntax order is mm/dd/yyyy. However, the proper format should be the international standard in which the date precedes the month. Another reason may be that you are attempting to insert a written month name but use a numeric month in the mask instead.

You may consider using the TO_DATE function to specify the date format mask. This function will convert a string to a date. The format for the TO_DATE function is:

TO_DATE( string1 [, format_mask] [, nls_language] )

The following is an example:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(15/07/99, ‘DD/MM/YY’);

This can also be written as:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(071599, ‘MMDDYY’);

The same example can be written yet another way:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(1999/07/15, ‘YYYY/MM/DD’);

All result in a date value of July 15, 1999.

Another option is that you can change the session’s date format with the following line of code. Users should be careful with choosing this option as it may have consequences for other SQL. It may be best to find the specific point of the code and correct the syntax of the month if this is not a frequent occurrence.

ALTER session set NLS_DATE_FORMAT=’DD/MM/YYYY’;

To avoid seeing error ORA-01843, be sure to write valid values for months. It is important to note that error ORA-01843 may not be thrown even when there is an error in month. Therefore, it is even more important to be aware of proper month values that are valid. Make sure that you are writing dates in correct NLS_DATE_FORMAT, especially if data is from the United States in which the standard differs from the worldwide standard practiced in other countries.