How to get year with fractional part from date in Oracle?

I need a PL/SQL function that takes a date and returns the year as a number, including a fractional part from the days. For example, 1995-01-01 would be 1995.000 and 2015-11-24 would be 2015.897. This is what I managed to produce:

CREATE OR REPLACE FUNCTION year_fraction (in_date DATE)
RETURN NUMBER
IS
  year NUMBER;
  days NUMBER;
  total_days NUMBER;
BEGIN
  year := EXTRACT(YEAR FROM in_date);
  days := in_date - TRUNC(in_date, 'YEAR');
  total_days := ADD_MONTHS(TRUNC(in_date, 'YEAR'), 12) - TRUNC(in_date, 'YEAR');
  RETURN year + days / total_days;
END year_fraction;

It works, but it feels like to much work to solve quite a simple problem. Can this be done in a neater way?

Answers


So you want the decimal part as percentage of days passed in current year. It would be a simple date arithmetic to divide the day of the year with total days in that year.

SQL> SELECT EXTRACT(YEAR FROM SYSDATE)
  2    ||'.'
  3    ||TRUNC(TO_NUMBER(TO_CHAR(SYSDATE, 'DDD'))/
  4    (add_months(TRUNC(SYSDATE,'year'), 12) - TRUNC(SYSDATE,'year'))*1000) my_format
  5  FROM DUAL;

MY_FORMAT
--------------------------------------------------------------------------------
2015.898

Eventually I ended up using this solution. It is quite similar to what I started out with in the question, but involves fewer function calls and is a bit more condensed.

CREATE OR REPLACE FUNCTION fractional_year (in_date DATE)
RETURN NUMBER
IS
  year DATE;
BEGIN
  year := TRUNC(in_date, 'YEAR');
  -- Year + Days passed so far / Total number of days in year
  RETURN EXTRACT(YEAR FROM year) + (in_date - year) / (ADD_MONTHS(year, 12) - year);
END fractional_year;

Need Your Help

Help with grasping (INNER?) JOIN

php mysql

I'm having trouble building a query. I can do what I want in 3 different queries.