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