How to convert number(16,10) to date in oracle

Question

Hi,
I'm trying to read the Borland Starteam application oracle database and I noticed that they represent their date as a number(16,10) column. I think it is not timestamp or epoch. For instance, I have the number: 37137.4347569444, how can I read it as date?

I saw that the database has a stored procedure, CONVERT_DATE:

CREATE OR REPLACE procedure STARBASE.convert_date 
      ( number_of_days IN integer 
         , nDate OUT number) 
is 
    nDateOffset number; 
    CurrentDate date; 
    Month integer; 
    Day integer; 
    year number; 
    success boolean := false; 
    bLeapYear boolean:=false;
    nDaysInMonths number; 
    nLeapDays integer; 
    fDate number (16,10); 
    rgMonthDays number(5,0); 
begin 
    select sysdate - number_of_days 
    into CurrentDate 
    from dual; 
    nDateOffset := 693959; 
    select to_number(substr((TO_CHAR (CurrentDate, 'MM-DD-YYYY')) , 1, 2), '99') - 1 
    into month 
    from dual; 
    select to_number(substr((TO_CHAR (CurrentDate, 'MM-DD-YYYY')) , 4, 2), '99') - 1 
    into day 
    from dual; 
    select to_number(substr((TO_CHAR (CurrentDate, 'MM-DD-YYYY')) , 7, 4), '9999') 
    into year 
    from dual; 
    if ( mod(year , 4) = 0 ) 
        and ( ( mod(year , 400) = 0) or ( mod(year , 100) <> 0 )) 
    then
        bLeapYear :=true; 
    end if; 
    nLeapDays := 0; 
    if ( bLeapYear = true) and ( Day = 28) and ( Month = 1 ) 
    then 
        nLeapDays := 1; 
    end if; 
    select substr(to_char(last_day(CurrentDate) , 'DD-MM-YYYY') , 1 , 2)
    into nDaysInMonths 
    from dual; 
    if Month = 0 then 
        rgMonthDays := 0; 
    elsif Month = 1 then 
         rgMonthDays := 31; 
    elsif Month = 2 then 
        rgMonthDays := 59; 
    elsif Month = 3 then 
        rgMonthDays := 90; 
    elsif Month = 4 then 
        rgMonthDays := 120; 
     elsif Month = 5 then 
         rgMonthDays := 151; 
     elsif Month = 6 then 
         rgMonthDays := 181; 
     elsif Month = 7 then 
         rgMonthDays := 212; 
     elsif Month = 8 then
         rgMonthDays := 243; 
     elsif Month = 9 then
         rgMonthDays := 273; 
     elsif Month = 10 then 
         rgMonthDays := 304; 
     elsif Month = 11 then 
         rgMonthDays := 334; 
     elsif Month = 12 then 
         rgMonthDays := 365; 
     end if;
     nDate := Year*365 + Year/4 - Year/100 + Year/400 + rgMonthDays + Day + 1; 
     if( Month < 2 ) and ( bLeapYear = true) then 
         nDate := nDate - 1;
     end if;
     nDate := nDate - nDateOffset; 
 exception 
     when others then raise; 
end convert_date;

I don't know how to use it.

how can i read it anyway? Please help. thank you

Answer 1

The number 693959 is the clue here. That is the number of days from 1/1/00 (the year zero never existed, but I digress) to 12/30/1899. That date was often used as the epoch for older databases where date was represented as a double. So something like this should put you back in business:

CREATE FUNCTION float_to_date(p_days NUMBER)
RETURN DATE IS
BEGIN
  RETURN to_date('1899-12-30', 'YYYY-MM-DD') + p_days;
END;

Test it of course. The number 37137.4347569444 should correspond to 2001-09-03 10:26:03.