时间戳

首页 > 解决方案 > 在 Oracle 中准确计算经过的年数，考虑闰年

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

问题描述

在 Oracle 中，我需要计算自给定日期以来经过的年数的基于 1 的值，并考虑闰年。

最初的计算没有考虑闰年。它是

CEIL ((SYSDATE- prog_start_dt) / 365))

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

CEIL(MONTHS_BETWEEN(SYSDATE, prog_start_dt) / 12)

错误是，如果我正好在 1 年之后，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。有没有好的解决方案？

标签： sqloracledateselect

解决方案

这是记录在案的行为：

如果date1和date2是该月的同一天或两个月的最后一天，则结果始终为整数。

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

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

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 |

推荐阅读