Convert decimal year to date

Question

I have dates in a table that are stored as decimal years. An example is 2003.024658 which translates to January 9, 2003. I would like to convert the decimal years to Oracle's date format.

---

I've found someone who's done this in Excel: Decimal year to date formula?

 =DATE(INT(B1),1,MOD(B1,1)*(DATE(INT(B1)+1,1,1)-DATE(INT(B1),1,1)))

However, I can't quite figure out how to convert the logic to Oracle PL/SQL.

Berkelium

Can you explain the logic behind conversion of 2003.024658 to January 9, 2003 ? – Stonwin

SELECT TO_DATE ('31-DEC-2002') + (0.024658 * 365) FROM DUAL gives output 09-Jan-2003. – Ammunition

Not needed. If it has helped you, that's enough. – Ammunition

Do please "accept" that answer so that others who want an answer know there is one, or those seeking to provide an answer don't get disappointed. – Montano

@Used_By_Already Yeah, the system makes me wait 2 days before accepting my own answer. – Berkelium

If you want the answer to be exact, it would depend on exactly how the decimal portion (0.024658) was originally calculated by whoever recorded the values in the first place - e.g. on a simple 365 days/year, or some average estimate like 365.24, or perhaps some other scheme. – Uremia

Answer 1

If you start from the assumption that the decimal portion was calculated according to the number of days in the given year (i.e. 365 or 366 depending on whether it was a leap year), you could do something like this:

with
 q1 as (select 2003.024658 d from dual)
,q2 as (select d
              ,mod(d,1) as decimal_portion
              ,to_date(to_char(d,'0000')||'0101','YYYYMMDD')
               as jan01
        from q1)
,q3 as (select q2.*
              ,add_months(jan01,12)-jan01 as days_in_year
        from q2)
select d
      ,decimal_portion * days_in_year as days
      ,jan01 + (decimal_portion * days_in_year) as result
from   q3;

d: 2003.024658
days: 9.00017
result: 10-JAN-2003 12:00am

Uremia