如何在甲骨文中计算两个日期之间的闰年数?

Question

我想计算员工雇用日期和当前日期之间的闰年数(在Oracle SQL Developer的hr.employees表上(。怎么做？

Answer 1

闰年由 366 天组成。 我假设两个日期之间的"闰年"包括一年中 1 月 1 日至 12 月 31 日的所有日子，即 2 月 29 日。

基于这种理解，有一个非常简单的解决方案。

• 计算雇用日期后一年的 1 月 1 日与结束日期当年的 1 月 1 日之间的天数。
• 计算这两个日期之间的年数。
• 减去天数和年数之差 * 365

令人高兴的是，内置函数完成了大部分工作。

这导致：

(trunc(sysdate, 'YYYY') -
trunc(hiredate + interval '1' year, 'YYYY') -
365 * (extract(year from sysdate) - extract(year from hiredate) - 1)
) as num_years

计算闰日有点棘手，但这不是问题要问的。

Answer 2

您可以重用 Oracle 已经编写的代码： 只需检查创建闰日是否引发异常：

SELECT TO_DATE('2016-02-29','YYYY-MM-DD') FROM DUAL;
29.02.2016 

但

SELECT TO_DATE('2018-02-29','YYYY-MM-DD') FROM DUAL;
ORA-01839: date not valid for month specified

所以你只需要计算例外：

CREATE OR REPLACE FUNCTION count_leap_years (p_from DATE, p_to DATE) RETURN NUMBER 
IS
number_of_leap_days NUMBER := 0;
date_not_valid EXCEPTION;
PRAGMA EXCEPTION_INIT(date_not_valid, -1839);
BEGIN
FOR y IN EXTRACT(YEAR FROM p_from) .. EXTRACT(YEAR FROM p_to) LOOP
DECLARE
d DATE;
BEGIN
d := TO_DATE(to_char(y,'fm0000')||'-02-29', 'YYYY-MM-DD');
IF p_from < d AND d < p_to THEN
number_of_leap_days := number_of_leap_days + 1;
END IF;
EXCEPTION WHEN date_not_valid THEN 
NULL;
END;
END LOOP;
RETURN number_of_leap_days;
END count_leap_years;
/

Answer 3

像这样的东西将允许您选择数字，在本例中，从 1 到 2999。

Select Rownum year
From dual
Connect By Rownum <= 2999

像这样的东西将允许您检查特定年份是否是闰年

CASE WHEN ((MOD(YEAR, 4) = 0 AND (MOD(YEAR, 100) <> 0)) OR MOD(year, 400) = 0) THEN 1 ELSE 0 END as isLeapYear

现在，您只需要添加过滤和闰年的总和。

Select sum(isLeapYear)
from (
Select year, CASE WHEN ((MOD(YEAR, 4) = 0 AND (MOD(YEAR, 100) <> 0)) OR MOD(year, 400) = 0) THEN 1 ELSE 0 END as isLeapYear
FROM (
Select Rownum year
From dual
Connect By Rownum <= 2999
) a
Where a.year >= EXTRACT(YEAR FROM DATE %DateStart%) and a.year <= EXTRACT(YEAR FROM DATE %DateEnd%)
) b

这在性能方面确实可以提高，但像这样，我认为更容易理解每个步骤在做什么，然后你可以将其分解成你真正想要和期望的东西。

Answer 4

试试这个：

CREATE OR REPLACE FUNCTION LEAP_YEARS(EMP_ID IN NUMBER)
RETURN NUMBER
IS 
HIRE_YEAR NUMBER:=0;
SYS_YEAR  NUMBER:=0;
NUMBER_LEAP_YEARS NUMBER:=0;
BEGIN
SELECT EXTRACT(YEAR FROM HIRE_DATE) INTO HIRE_YEAR
FROM EMPLOYEE
WHERE EMPLOYEE_ID = EMP_ID;
SELECT EXTRACT(YEAR FROM SYSDATE()) INTO SYS_YEAR
FROM DUAL;
FOR IDX IN HIRE_YEAR..SYS_YEAR LOOP
IF MOD(IDX,4)=0 THEN
NUMBER_LEAP_YEARS := NUMBER_LEAP_YEARS  + 1;
END IF;
END LOOP;
RETURN NUMBER_LEAP_YEARS;
END;

我在一个玩具例子上测试了它，它有效。也许你需要改进它(提示：也许它需要一些异常，它假设hire_date <sysdate(。>

然后，您可以将其用作：

SELECT LEAP_YEARS(E.EMPLOYEE_ID) FROM EMPLOYEE E;