如何在oracle中使用YEARFRAC

hud 发表于 Dev

皮肤

我想在 oracle 数据库中实现 Excel 表的一种逻辑。

我YEARFRAC()在 Oracle 中的存储过程中使用，但出现错误

错误（166,22）：PLS-00201：必须声明标识符“YEARFRAC”

这是我的代码

v_STD_REVISED_AMT := YEARFRAC('01-04-2016', V_FINALSRDATE, 'dd-mm-yy');

请建议如何实现这一点，或者有没有其他方法可以YEARFRAC在oracle中实现。

更新

我的第二个参数值是 07-05-2018

MT0

YEARFRAC 是一个 Excel 函数（和其他支持的电子表格）。

正如您的错误所述：

Error(166,22): PLS-00201: identifier 'YEARFRAC' must be declared

该YEARFRAC函数未在 Oracle 中定义，您无法使用它。

如果您想要等效的东西，那么您将需要编写一个自定义函数。

（注意：Excel 函数不使用默认的第三个参数计算日期和年份之间的实际天数，而是使用美国 (NASD) 30/360 计算天数的方法；如果您天真地只计算天数，那么输出将与 Excel 不匹配。）

如果您想要默认的 US (NADS) 30/360 行为，YEARFRAC则：

CREATE FUNCTION YEARFRAC(
  start_date IN DATE,
  end_date   IN DATE
) RETURN NUMBER DETERMINISTIC
IS
  $IF DBMS_DB_VERSION.ver_le_11 $THEN
  $ELSE
    PRAGMA UDF;
  $END
  y1 PLS_INTEGER := EXTRACT(YEAR  FROM start_date);
  m1 PLS_INTEGER := EXTRACT(MONTH FROM start_date);
  d1 PLS_INTEGER := EXTRACT(DAY   FROM start_date);
  y2 PLS_INTEGER := EXTRACT(YEAR  FROM end_date);
  m2 PLS_INTEGER := EXTRACT(MONTH FROM end_date);
  d2 PLS_INTEGER := EXTRACT(DAY   FROM end_date);
BEGIN
  IF m1 = 2 AND TRUNC(start_date) = LAST_DAY(TRUNC(start_date)) THEN
    IF m2 = 2 AND TRUNC(end_date) = LAST_DAY(TRUNC(end_date)) THEN
      d2 := 30;
    END IF;
    d1 := 30;
  END IF;
  IF d2 = 31 AND d1 >= 30 THEN
    d2 := 30;
  END IF;
  IF d1 = 31 THEN
    d1 := 30;
  END IF;
  RETURN (360*(y2-y1)+30*(m2-m1)+d2-d1)/360;
END yearfrac;
/

然后：

WITH date_range ( start_date, end_date ) AS (
  SELECT DATE '2021-01-07', DATE '2021-07-07' FROM DUAL UNION ALL
  SELECT DATE '2021-01-01', DATE '2021-02-01' FROM DUAL UNION ALL
  SELECT DATE '2021-01-01', DATE '2021-12-30' FROM DUAL UNION ALL
  SELECT DATE '2021-01-01', DATE '2021-12-31' FROM DUAL
)
SELECT start_date,
       end_date,
       yearfrac( start_date, end_date )
FROM   date_range

输出：

开始日期结束日期 YEARFRAC_DAYS30_360

07-JAN-21 07-JUL-21 0.5

01-JAN-21 01-FEB-21 0.0833333333333333

01-JAN-21 21 年 12 月 30 日 0.997222222222222

01-JAN-21 21 年 12 月 31 日 1