Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: Oracle and Excel dates

    I am attempting to query an Oracle db using MS Excel. I am using a datetime field to set criteria for a date range. It works fine when I use in Microsoft query, however when I reference a field in the spreadsheet it fails with a message that the numeric field is not right. How/can I reference a cell containing a date used as critera for the query?
    Thanks,
    Lee

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you for such a question!

    I've never used Excel to extract data from an Oracle database, but now I tried to do so. Click here and click there, and here it is - my first spreadsheet as a result of your question.

    Next step was to include a criteria into a query - that is difficult for me. I ran Microsoft Query, got a table, saw a query and it works fine when I say something like "... WHERE deptno = 10", but - HOW TO REFERENCE a cell in there?

    WHERE deptno = G1 / $G$1 / Sheet1!$G$1 / ...#!#%%"# (read: drives me crazy). Google might be your friend, but not mine - I simply can't find how to reference a cell. If you do know, would you mind to share it?

    Once it is done, I suspect that Oracle requires valid datatype conversion. Perhaps it would be better to leave your "datetime" column an ordinary "text" column (cell) so that you'd be sure that Jan 23rd 2007 is "23.01.2007" and not "23.1.2007" (note the difference in month notation).

    Then, I guess, it would be necessary to tell Oracle what to do with this text value: ... WHERE date_column = TO_DATE(cell_reference, 'dd.mm.yyyy'). Make sure you use the same format as you've used when putting date value into a cell.

    I'm not sure will it be helpful, but ... in a few iterations, maybe.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    An excel date field is simply a numeric field with special formatiing. The following select will populate it correctly.

    select s.FINAL_DT - to_date('12/30/1899','mm/dd/yyyy') FINAL_DATE
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    As far as I remember, I have used Excel again'st Oracle databases and to include parameters, I used a ? in the query definition.. I must be wrong though (been years since I did this).

  5. #5
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Thanks for the replies! I tried the To_Date(xx,xx) and it worked fine for entering the date into the prompt message box when using [?] in MS Query. However, it still will not work when binding the criteria to a cell. You would think that there would be a fix for the cell method.
    Thanks,
    Lee

Posting Permissions

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