Excel Query Error from Oracle

If you don't force the format Oracle will try to guess.  When there is a mismatch between the data provided and what Oracle guesses the format is, you can get this type of error.

Better to format the query to match the data in the cell.

You normally do this using TO_DATE in the select.

Example to_date ('01/01/2001','MM/DD/YYYY')

If you can provide what the data looks like in the cell and the query you are executing, we can probably help you out.

To force the date format in the query to match your cell, you can do like this as an example in your query:

to_char(column_name,'MM/DD/YYYY') -- Column name of your Oracle table.

Select allOpen in new window

Just replace the date format to what you really need, based on your excel file.

This may resolve the issue.

I am using the date from a Parameter where I plug a value into a cell and get the value from that cell for the query.

Basically I have the query set up to a effective date - I can go in and put a new date in the cell of the workbook or spreadsheet and then refresh my data.

can you provide us a sample of what the data looks like in the cell?

Looks like the day and month sequence are wrong. It may look at the day value for the month.

If you can also give us what the query look like, that would also help.

From what I gather your asking, you have a parameter database / web query in an Excel sheet that updates when you change the value. Since Oracle generally looks for "DD-MON-YYYY" date format, your query seems to be failing with the default Excel format. I would suggest updating an adjacent cell with the following formula in the cell your parameter query is pointed to =TEXT(CELL_REFERENCE,"DD-MMM-YYYY"). This way you will provide your query what Oracle is looking for.

>>Basically I have the query set up to a effective date - I can go in and put a new date in the cell of the workbook or spreadsheet and then refresh my data.

I understand that.  The issue is that if you aren't using TO_DATE and a format mask that EXACTLY matches the value in the cell, Oracle can get confused.

Here is an example of one such query:

SELECT wh_acctcommon.ACCTNBR, wh_acctcommon.EFFDATE, wh_acctcommon.MJACCTTYPCD, wh_acctcommon.PRODUCT, wh_acctcommon.CURRACCTSTATCD, wh_acctcommon.NOTEBAL, wh_acctcommon.CURRMIACCTTYPCD
FROM OSIBANK.wh_acctcommon wh_acctcommon
WHERE wh_acctcommon.EFFDATE= ?
AND (wh_acctcommon.MJACCTTYPCD In ('SAV','TD','CK')) AND (wh_acctcommon.CURRACCTSTATCD Not In ('CLS','CO')) AND (wh_acctcommon.NOTEBAL>=0) AND (wh_acctcommon.CURRMIACCTTYPCD Not In ('CKCO','SACO'))

Then the parameter is set to reference a cell (K1 in this case). That cell looks like this: 20-Feb-15 and has this for a formula:
=text(L1,"DD-MMM-YY")

Cell L1 allows the user to type in the date in a format they are used to mm/dd/yyyy

So that covers what c336914 suggested. And that worked up until this week, when we switched the machine from Excel 2010 to Excel 2013. Database did not change

>>"DD-MMM-YY"

This isn't a valid format mask.  The 'default' for Oracle is 'DD-Mon-YY'

>>And that worked up until this week, when we switched the machine from Excel 2010 to Excel 2013.

It is all about the format of the column value and getting that into something Oracle can use.

Try formatting the date column in Excel.  Then add to_date with a format mask that matches.

https://support.office.com/en-ca/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e

Slightwv-

Sorry I don't follow. Using the sql statement above and what you in the attachment, can you tell me where to insert the to_date command?
screen.JPG

Thank you slightwv - much appreciated.