SQL> alter session set nls_date_Format = 'dd.mm.yyyy bc';

Session altered.

SQL> select trunc(sysdate) - 2454482 from dual;

TRUNC(SYSDATE)
--------------
01.01.4712 pne

SQL>

SQL> create table test (col number);

Table created.

SQL> insert into test (col) values
  2  (to_char(to_date('30.09.2000', 'dd.mm.yyyy'), 'J'));

1 row created.

SQL> select col from test;

             COL
----------------
         2451818

SQL> select to_date(col, 'J') from test;

TO_DATE(CO
----------
30.09.2000

SQL>

  FUNCTION convert_number_to_time (
    p_time  IN NUMBER
  )
  RETURN VARCHAR2  
  IS 
   ln_time  NUMBER;
   ln_hour  NUMBER;
   ln_min   NUMBER;
   ln_sec   NUMBER;  
  BEGIN
    ln_hour := TRUNC(p_time/3600);
    ln_time := p_time - (ln_hour * 3600);
    -- 
    IF ln_time = 0 THEN
      ln_min := 0;
      ln_sec := 0;
    ELSE 
      ln_min  := TRUNC(ln_time/60);
      ln_time := ln_time - (ln_min * 60); 
      ln_sec  := ln_time;
    END IF;
    --
    RETURN  LTRIM(TO_CHAR(ln_hour,'00')) 
            || ':' || LTRIM(TO_CHAR(ln_min,'00'))
            || ':' || LTRIM(TO_CHAR(ln_sec,'00'));
  END convert_number_to_time;
  --
  FUNCTION convert_time_to_number (
    p_time  IN VARCHAR2
  ) 
  RETURN NUMBER 
  IS
   lv_time  VARCHAR2(10);
   lv_hour  VARCHAR2(2);
   lv_min   VARCHAR2(2);
   lv_sec   VARCHAR2(2);
   --
   ln_out   NUMBER;
  BEGIN
    lv_time := p_time;
    --
    -- Find hous
    --
        lv_hour := SUBSTR(lv_time,1,INSTR(lv_time,':')-1);
    lv_time := SUBSTR(lv_time,INSTR(lv_time,':')+1);
    --
    -- Find Minute
    --
    lv_min  := SUBSTR(lv_time,1,INSTR(lv_time,':')-1);
    lv_time := SUBSTR(lv_time,INSTR(lv_time,':')+1);    
    --
    -- Find second
    --
    lv_sec  := lv_time;
    --
    --dbms_output.put_line('Hour : ' || lv_hour);
    --dbms_output.put_line('Min : ' || lv_min);
    --dbms_output.put_line('Sec : ' || lv_sec);        
    --
    RETURN (NVL(TO_NUMBER(lv_hour),0) * 3600) + 
           (NVL(TO_NUMBER(lv_min),0) * 60) + 
           (NVL(TO_NUMBER(lv_sec),0));
  END convert_time_to_number;