Database Reference
In-Depth Information
ERROR at line 1:
ORA-01839: date not valid for month specified
EODA@ORA12CR1> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
3 /
select dt, dt+numtoyminterval(1,'month')
*
ERROR at line 1:
ORA-01839: date not valid for month specified
In my experience, this makes using a month interval in date arithmetic impossible in general. A similar issue
arises with a year interval: adding one year to February 29, 2000, results in a runtime error because there is no
February 29, 2001.
Getting the Difference Between Two DATEs
Another frequently asked question is, “How do I retrieve the difference between two dates?” The answer is deceptively
simple: you just subtract them. This will return a number representing the number of days between the two dates.
Additionally, you have the built-in function
MONTHS_BETWEEN
that will return a number representing the number
of months—including fractional months—between two dates. Lastly, with the
INTERVAL
datatypes, you have yet
another method to see the elapsed time between two dates. The following SQL query demonstrates the outcome of
subtracting two dates (showing the number of days between them), using the
MONTHS_BETWEEN
function and then the
two functions used with
INTERVAL
types:
EODA@ORA12CR1> select dt2-dt1 ,
2 months_between(dt2,dt1) months_btwn,
3 numtodsinterval(dt2-dt1,'day') days,
4 numtoyminterval(trunc(months_between(dt2,dt1)),'month') months
5 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
6 to_date('15-mar-2001 11:22:33','dd-mon-yyyy hh24:mi:ss') dt2
7 from dual )
8 /
DT2-DT1 MONTHS_BTWN DAYS MONTHS
---------- ----------- ------------------------------ -------------
380.430903 12.5622872 +000000380 10:20:30.000000000 +000000001-00
Those are all correct values, but not of great use to us yet. Most applications would like to display the years,
months, days, hours, minutes, and seconds between the dates. Using a combination of the preceding functions,
we can achieve that goal. We'll select out two intervals: one for the years and months, and the other for just the
day, hours, and so on. We'll use the
MONTHS_BETWEEN
built-in function to determine the decimal number of months
between the two dates, and then we'll use the
NUMTOYMINTERVAL
built-in function to convert that number into the
years and months. Additionally, we'll use
MONTHS_BETWEEN
to subtract the integer number of months between the two
dates from the larger of the two dates to get down to the days and hours between them:
EODA@ORA12CR1> select numtoyminterval
2 (trunc(months_between(dt2,dt1)),'month')
3 years_months,
4 numtodsinterval
5 (dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),
