CODEWITHSUNDEEP
oracle-databaseoracle11g
jase sykes
jase sykes

Reputation: 206

Oracle ORA-01839: date not valid for month specified Leap Year

Oracle 11g

here is a quick one hopefully.

Below is part of a script that gets date only from from the next month first day of next month to last day. But today 29th feb it thrown an error of ORA-01839: date not valid for month specified 

M.MS_DATE between trunc(sysdate + interval '1' month,'MM') and last_day(sysdate + interval '1' month)

Is there a way round this. Many thanks

Upvotes: 2

Views: 5433

Answers (2)

Alex Poole
Alex Poole

Reputation: 191415

I would probably use add_months() as a_horse_with_no_name suggests, but just as an alternative if you want to use intervals, you can move the point you do the truncation in the first expression, and include the same truncation in the second expression:

select trunc(sysdate, 'MM') + interval '1' month as first_day,
  last_day(trunc(sysdate, 'MM') + interval '1' month) as last_day
from dual;

FIRST_DAY  LAST_DAY  
---------- ----------
2015-02-01 2015-02-28

This works because all months have a first day, so you don't trip over the documented caveat.

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error

Upvotes: 1

user330315
user330315

Reputation:

I have seen this as well and I consider this a bug in Oracle.

The workaround is to use add_months() instead :

between trunc(add_months(sysdate,1),'MM') and last_day(add_months(sysdate,1));

Upvotes: 7

Related Questions