convert Oracle time field

sutjh

New Member
Joined
Mar 9, 2002
Messages
15
I am retrieving a field which is stored in a backend Oracle database as a 5 figure 'general' time field .I've tried reformatting in the query, and also reformatting the field after retrieval, but I can't get Excel to understand it.
The application database displays the field as 'hh:mm'.
Ultimately, I want to concatenate this field with a corresponding date field, to use the exact time/date, to query for only the stuff I haven't already processed. The date field was 'date/time' and excel had no problems with it .
Unlike Excel, the serial number in the Oracle time field is a whole number.
Has anyone else come across this problem ?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi

Can you give a relavent example of how a particular time look in Excel AFTER it's been imported. It would also help if you supplied the time is should be.
 
Upvote 0
format([BOOK_TIME],"hh:mm")

gives 00:00 for EVERY serial number

"hh:mm:ss"
= 00:00:00: - ditto

more interestingly,
format([BOOK_TIME],"c")

gives dates like 12/05/2145, 01/07/2034 !!?

reformatting the cells in Excel produces the exactly the same results (I guess it would, really..)

I've tried all the functions, including add-ins Analysis Tool-Pak, but none were really suitable.

I will make dummy entries later on today when back at work, for midnight, midday, 3 o'clock, and 6 o'clock and a few half hour entries , query the database for these entries and post the corresponding serial numbers..

I won't give up, as I am sure Oracle don't use random numbers !!

Thanks for being there.......
 
Upvote 0
I must say this is the first time I've heard of this problem, but then I dont wander too far from Excel/VBA either.

If you are constantly getting 00:00 this would strongly indicate that the time is a number greater than, or equal to 1
Excel stores times as decimal fractions, it sees times as a portion of a day, with 1 being a whole day. It sees dates as serial numbers with 1 being the date 1/1/1900, 2 being 1/2/1900 etc. If you enter a Date and a Time is the same cell you get decimal value added to the date, this represents the time of the day for that date. For eaxmple the date and time of "1/1/1900 18:00" has an underlying value of 1.75

The fact you ended up with "01/07/2034" for a time suggest that oracle is storing this time as "49126". I say this because the date 01/07/2034 is 49125 days ahead of 1/1/1900 (1)


In a nutshell, once we find out the method Oracle uses it should only be matter of finding out the expression needed to convert it.


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-12 22:15
 
Upvote 0
I think you are exactly right !

Searching has brought little to light on Oracle's methods, and I think I my only hope is to work out the serial number's relationship with a time value.

I was hoping someone had done this before (surely I am not the only person in the world who has to analyse in Excel from Oracle ?)!

I can't say I have had much experience in resolving expressions, I guess I'll just get a few numbers from both sides of the equation
and go from there ?
 
Upvote 0
Dave, yeh, just a few !

Maybe my wording wasn't the best, though...

The datatype is 'general', and using words like 'convert', 'Oracle', 'Excel', 'Time',
'Date' just gets very 'general' hits ! After ploughing through 5 pages of 100,000 sites and finding nothing remotely relevant, I felt it was time to try the experts !
 
Upvote 0
There is a very good guy on this site called "Ivan" try posting a question with his name in the Subject, he may well know the answer to this.
 
Upvote 0
Solved - turns out that any Oracle serial number divided by an Excel serial number always equals 86400 -the number of seconds in a day.

Now I can query for data with this concatenated field

Format([BOOK_DATE]++([BOOK_TIME]/86400),"c")

which gives me a figure which Excel can format.

Thanks for all help !
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top