Excel Query Error from Oracle

When attempting to Query an oracle Database using Excel 2013 64 bit and 32 bit I receive the following error:
oracle odbc ora ora-01843 not a valid month
In Excel 2010 we could correct this issue by formatting the date cell as text. This no longer works in Excel 2013
GBTISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
Wilder1626Commented:
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.

Open in new window

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

This may resolve the issue.
GBTISAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wilder1626Commented:
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.
Bruce CadizQuality SpecialistCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
GBTISAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>"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
GBTISAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Try this:
...
 wh_acctcommon.EFFDATE= to_date(?,'DD-MON-YY')
...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GBTISAuthor Commented:
Thank you slightwv - much appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.