Re: Convert Number to Time ?

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3cecebd3$0$8513$ed9e5944_at_reading.news.pipex.net>...
> You'll need to find out what the numbers mean. It may be simply the elapsed
> time in seconds since a certain date or it may be something else. We have an
> app that has a 'date' columns that is calculated as 8*day number + 64*month
> number + 256*year The only way to convert is to find out what the algorithm
> for converting dates into numbers is in the app that talks to this database.
>
> Once you have done that, and verified the conversion, of course you can
> shoot the developer for using the wrong datatype.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Jon Spartan" <jonu_at_ixtech.net> wrote in message
> news:a58oeu4quio3817du088vpsk4k6gga41hn_at_4ax.com...
> > I have an Oracle 8.1.7 DB I must work with and import into MS-SQL. The
> > "programmer" has a column TIME ( which I will allow should be some for
> > od time or date/time ) which is listed as "number" with a precision of
> > 21.
> >
> > I have to convert this to an MS-SQL datetime column.
> >
> > Is there anyone who may assist me in this, as I am at a total loss as
> > Oracle is very new to me ( 4 days and counting )
> >
> > Many thanks in advance
> >
> > Jon Spartan
> >

Jon, I think Niall is correct in that you need to know what how the data is being recorded. I would start with trying to determine how the programmer creates the value; it could be number of seconds since midnight as this a fairly common way of doing time.

Here is Oracle SQL that will convert Oracle Dates to and from a number; In this case that number is the start date and time for C, Jan. 1, 1970. I think different MS products use different start dates/time as I am pretty sure Excell use Jan. 1, 1900.

--
--  Convert Oracle date to number of seconds since reference date  MDP
--
select to_number(sysdate - to_date('01-JAN-1970','DD-MON-YYYY'))
       * (24 * 60 * 60)  "C Time"
from sys.dual
;
--
--  Convert from number of seconds since reference date to Oracle date
 MDP
--
select to_char(to_date('01-JAN-1970','DD-MON-YYYY') +
       ( &no_sec_since_70 / (60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS')
       "New Date"
from   sys.dual
;

HTH -- Mark D Powell --