Not A Valid Month In Oracle When Add_months Is Used

I have given the below code in query. where to_date(cal_wid,'yyyymmdd') between add_months(to_date(sysdate, 'MM-YYYY'),-12) and to_date(sysdate, 'MM-YYYY') I a

Solution 1:

Never, ever use TO_DATE() on something that is already a DATE. The reason for this is because Oracle will have to do some implicit conversions in order to follow your wishes:

TO_DATE(sysdate, 'mm-yyyy')

is really run as

TO_DATE(TO_CHAR(sysdate, '<default nls_date_format parameter>'), 'mm-yyyy')

so if your nls_date_format is set to something that's other than 'mm-yyyy', you're going to have problems. The default nls_date_format parameter is 'DD-MON-YY', which is more than likely the value yours is set to.

If all you wanted to do was to add_months to the 1st of the current month, then you should use TRUNC(), eg:

add_months(trunc(sysdate, 'MM'),-12)

---

Here's proof of the implicit to_char if you to_date something that's already a date, as requested by Lalit - an execution plan of a basic query involving to_date(sysdate):

SQL_ID  3vs3gzyx2gtcn, child number 0-------------------------------------select*from   dual where  to_date(sysdate) < sysdate

Plan hash value: 3752461848----------------------------------------------------------------------------| Id  | Operation          | Name | E-Rows|E-Bytes| Cost (%CPU)| E-Time|----------------------------------------------------------------------------|0|SELECT STATEMENT   ||||2 (100)|||*1|FILTER|||||||2|TABLE ACCESS FULL| DUAL |1|2|2   (0)|00:00:01|----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------1-filter(TO_DATE(TO_CHAR(SYSDATE@!))<SYSDATE@!)

You can clearly see the TO_CHAR() in the filter condition.

Solution 2:

The error is here:

to_date(sysdate, 'MM-YYYY')

Let's see why:

SQL>select to_date(sysdate, 'MM-YYYY') from dual;
select to_date(sysdate, 'MM-YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid monthSQL>

You don't need to convert sysdate again into a date. Remove to_date and just use SYSDATE.

NOTE As an additional information, please look at @Boneist's answer for an insight regarding the implicit conversion that Oracle is forced to do while applying to_date on SYSDATE.

You use to_date to convert a string into a date. For date calculations, you just need to use the date value.

For example,

SQL>WITH DATA AS(
  2SELECT'20150101' cal_wid FROM DUAL
  3  )
  4SELECT*5FROM DATA
  6WHERE   to_date(cal_wid,'yyyymmdd')
  7BETWEEN add_months(SYSDATE,-12)
  8AND     SYSDATE
  9/

CAL_WID
--------20150101SQL>

Modify your query as:

WHERE to_date(cal_wid,'yyyymmdd') BETWEEN add_months(SYSDATE, -12) AND SYSDATE