Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: convert Oracle time field

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?





  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !




  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  10. #10
    Guest

    Default

    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 !

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •