sql - 在 Oracle 中准确计算经过的年数，考虑闰年

Question

在 Oracle 中，我需要计算自给定日期以来经过的年数的基于 1 的值，闰年的会计处理 .
最初的计算没有考虑闰年。它是

CEIL ((SYSDATE- prog_start_dt) / 365))

然后我决定使用 MONTHS_BETWEEN ，但我的新计算中有一个错误:

CEIL(MONTHS_BETWEEN(SYSDATE, prog_start_dt) / 12)

错误是，如果我正好在 prog_start_date 一年之后无论时间如何，该值都少 1。前任。:

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

正确的结果是 2 .所以我需要顶级公式的行为，但要考虑闰年，这显然不会是 365 .有没有好的解决办法？

Answer 1

这是 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.

如果你真的想解决这个问题，那么一个选择是添加一些逻辑来处理这个特定情况:当被比较的日期属于同一个月和同一天时，然后检查时间部分以查看年份是否已经过去:

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 |