By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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,
(
360 * (EXTRACT(YEAR FROM end_date) - EXTRACT(YEAR FROM start_date))
+ 30 * (EXTRACT(MONTH FROM end_date) - EXTRACT(MONTH FROM start_date))
+ EXTRACT(DAY FROM end_date) - EXTRACT(DAY FROM start_date)
) / 360 AS yearfrac_days30_360
FROM date_range
| START_DATE | END_DATE | YEARFRAC_DAYS30_360 |
|---|---|---|
| 07-JAN-21 | 07-JUL-21 | .5 |
| 01-JAN-21 | 01-FEB-21 | .0833333333333333333333333333333333333333 |
| 01-JAN-21 | 30-DEC-21 | .9972222222222222222222222222222222222222 |
| 01-JAN-21 | 31-DEC-21 | 1 |
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,
(
360 * (EXTRACT(YEAR FROM end_date) - EXTRACT(YEAR FROM start_date))
+ 30 * (EXTRACT(MONTH FROM end_date) - EXTRACT(MONTH FROM start_date))
+ EXTRACT(DAY FROM end_date) - EXTRACT(DAY FROM start_date)
) / 360 AS yearfrac_days30_360,
yearfrac( start_date, end_date )
FROM date_range
| START_DATE | END_DATE | YEARFRAC_DAYS30_360 | YEARFRAC(START_DATE,END_DATE) |
|---|---|---|---|
| 07-JAN-21 | 07-JUL-21 | .5 | .5 |
| 01-JAN-21 | 01-FEB-21 | .0833333333333333333333333333333333333333 | .0833333333333333333333333333333333333333 |
| 01-JAN-21 | 30-DEC-21 | .9972222222222222222222222222222222222222 | .9972222222222222222222222222222222222222 |
| 01-JAN-21 | 31-DEC-21 | 1 | 1 |