Accurately calculate number of years elapsed, accounting for leap years, in Oracle

user_1005607 0
Viewed 159

In Oracle, I need to calculate a 1-based value of the number of years that have elapsed since a given date, accounting for leap years.

The original calculation did not account for leap years. It was

CEIL ((SYSDATE- prog_start_dt) / 365))

Then I decided to use MONTHS_BETWEEN, but there is a bug in my new calculation:

CEIL(MONTHS_BETWEEN(SYSDATE, prog_start_dt) / 12)

The bug is that if I'm exactly 1 year after prog_start_date regardless of time, the value is 1 less. Ex.:

sysdate = 2020-07-01 15:30:00
prog_start_dt = 2019-07-01 00:00:00
--> 1. CEIL ((SYSDATE- prog_start_dt) / 365)) --> Result: 2
--> 2. CEIL(MONTHS_BETWEEN(SYSDATE, prog_start_dt) / 12) --> Result 1

The correct result is 2. So I need the behavior of the top formula, but accounting for leap years, which obviously won't be 365. Is there a good solution?

select date oracle sql
2 Answers

This is a documented behavior:

If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer.

If you really want to work around this, then one option is to add some logic to handle this specific case: when dates being compared belong to the same month and day, then check the time portion to see if the year is yet elapsed:

ceil(months_between(sysdate, prog_start_dt) / 12)
+ case when to_char(sysdate, 'mm-dd') = to_char(prog_start_dt, 'mm-dd') 
       and to_char(sysdate, 'hh24:mi') > to_char(prog_start_dt, 'hh24:mi')
    then 1
    else 0
end

Demo on DB Fiddle:

with t as (
    select 
        to_date('2020-07-01 15:30:00', 'yyyy-mm-dd hh24:mi:ss') date1, 
        to_date('2019-07-01 00:00:00' , 'yyyy-mm-dd hh24:mi:ss') date2
    from dual
)
select 
    ceil(months_between(date1, date2) / 12)
    + case when to_char(date1, 'mm-dd') = to_char(date2, 'mm-dd') 
           and to_char(date1, 'hh24:mi') > to_char(date2, 'hh24:mi')
        then 1
        else 0
    end year_diff
from t

| YEAR_DIFF |
| --------: |
|         2 |
user_106767160

From the documentation on MONTHS_BETWEEN:

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

You can "fix" MONTHS_BETWEEN so it does not always use integer values when the day-of-the-month is the same (or if they are both last days of the month) using:

MONTHS_BETWEEN( end_date, start_date )
+
CASE
WHEN EXTRACT( DAY FROM start_date ) = EXTRACT( DAY FROM end_date )
OR   ( start_date = LAST_DAY( start_date ) AND end_date = LAST_DAY( end_date ) )
THEN ( end_date
     - ADD_MONTHS( start_date, MONTHS_BETWEEN( end_date, start_date ) )
     ) / 31
ELSE 0
END

So, your query would be:

SELECT start_date,
       end_date,
       CEIL(
         (
         MONTHS_BETWEEN( end_date, start_date )
         +
         CASE
         WHEN EXTRACT( DAY FROM start_date ) = EXTRACT( DAY FROM end_date )
         OR   ( start_date = LAST_DAY( start_date ) AND end_date = LAST_DAY( end_date ) )
         THEN ( end_date
                - ADD_MONTHS( start_date, MONTHS_BETWEEN( end_date, start_date ) )
              ) / 31
         ELSE 0
         END
         )
         / 12
       ) AS years
FROM   test_data;

And for some test data:

CREATE TABLE test_data ( start_date, end_date ) AS
  SELECT DATE '2019-07-01',
         DATE '2020-07-01' + INTERVAL '15:30:00' HOUR TO SECOND
  FROM   DUAL
UNION ALL
  SELECT DATE '2019-07-31',
         DATE '2020-02-29' + INTERVAL '15:30:00' HOUR TO SECOND
  FROM   DUAL
UNION ALL
  SELECT DATE '2019-02-28',
         DATE '2020-02-29' + INTERVAL '15:30:00' HOUR TO SECOND
  FROM   DUAL;

This outputs:

START_DATE          | END_DATE            | YEARS
:------------------ | :------------------ | ----:
2019-07-01 00:00:00 | 2020-07-01 15:30:00 |     2
2019-07-31 00:00:00 | 2020-02-29 15:30:00 |     1
2019-02-28 00:00:00 | 2020-02-29 15:30:00 |     2

db<>fiddle here

user_15092640
Related