convert Oracle time field

sutjh · Mar 12th, 2002, 09:55 PM

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 ?

Dave Hawley · Mar 12th, 2002, 10:14 PM

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.

sutjh · Mar 12th, 2002, 10:43 PM

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.......

Dave Hawley · Mar 12th, 2002, 11:08 PM

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

[ This Message was edited by: Dave Hawley on 2002-03-12 22:15 ]

sutjh · Mar 12th, 2002, 11:38 PM

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 ?

Dave Hawley · Mar 12th, 2002, 11:41 PM

sutjh, have you tried a search on google.com, I am sure (as you say) this has been done before.

sutjh · Mar 13th, 2002, 12:09 AM

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 !

Dave Hawley · Mar 13th, 2002, 12:20 AM

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.

sutjh · Mar 13th, 2002, 05:47 PM

Mar 13th, 2002, 05:57 PM

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 !

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 09:55 PM	#1
sutjh New Member Join Date: Mar 2002 Posts: 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 ?

Mar 12th, 2002, 10:14 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 12th, 2002, 10:43 PM	#3
sutjh New Member Join Date: Mar 2002 Posts: 15	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.......

Mar 12th, 2002, 11:08 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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 [ This Message was edited by: Dave Hawley on 2002-03-12 22:15 ]

Mar 12th, 2002, 11:38 PM	#5
sutjh New Member Join Date: Mar 2002 Posts: 15	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 ?

Mar 12th, 2002, 11:41 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	sutjh, have you tried a search on google.com, I am sure (as you say) this has been done before.

Mar 13th, 2002, 12:09 AM	#7
sutjh New Member Join Date: Mar 2002 Posts: 15	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 !

Mar 13th, 2002, 12:20 AM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 13th, 2002, 05:47 PM	#9
sutjh New Member Join Date: Mar 2002 Posts: 15

Mar 13th, 2002, 05:57 PM	#10
Guest Posts: n/a	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 !