CODEWITHSUNDEEP
sqloracle-database
Akhilesh
Akhilesh

Reputation: 524

ORA-01839: date not valid for month specified

The following query returns 

select to_char( trunc(sysdate) - numtoyminterval(level - 1, 'month'), 'mon-yy') as month from dual connect by level <= 12

last 12 months according to today's date(i.e. 2-Jan-18).

enter image description here

Say if today's date is 29-DEC-17 it gives oracle sql error: ORA-01839: date not valid for month specified (since on subtracting there would be a date in the result as '29-FEB-17' which is not possible). So on specific dates this error would pop-up. How do you suggest to overcome this?

Upvotes: 0

Views: 4865

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

This behavior of INTERVAL YEAR TO MONTH is as documented, see Datetime/Interval Arithmetic

You should consider function ADD_MONTHS:

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

It depends on your requirements what you consider as "right". In fact "one month" does not have a fixed duration.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142720

I prefer ADD_MONTHS (TRUNC of a date literal is stupid, but I left it as you'd have SYSDATE anyway, wouldn't you?):

SQL> select to_char(add_months(trunc(date '2017-12-29'), -level), 'dd-mon-yy',
  2                 'nls_Date_language = english') as month
  3  from dual
  4  connect by level <= 12;

MONTH
------------------
29-nov-17
29-oct-17
29-sep-17
29-aug-17
29-jul-17
29-jun-17
29-may-17
29-apr-17
29-mar-17
28-feb-17
29-jan-17
29-dec-16

12 rows selected.

SQL>

Upvotes: 1

Related Questions