如何在oracle中将number(16,10)转换为date

Question

我正在尝试读取Borland Starteam应用程序oracle数据库，我注意到它们将日期表示为数字(16，10)列。我认为它不是时间戳或纪元。例如，我有一个数字: 37137.4347569444，我怎么能把它读成日期呢？

我看到数据库有一个存储过程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;

我不知道怎么用它。

我怎么才能读懂它呢？请帮帮忙。谢谢

Answer 1

数字693959是这里的线索。这是从1/1/00 (零年从来不存在，但我离题了)到12/30/1899的天数。该日期通常被用作旧数据库的纪元，其中日期表示为双精度。所以像这样的东西应该会让你重回正轨：

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

当然是测试一下。数字37137.4347569444应对应于2001-09-03 10:26:03。