SQL data conversion error for Excel VBA

[nataliek92]

Hello

I would like to copy a recordset from a Oracle RDB Driver into Excel, however I am having problems with my DateTime variable. I would like to open an SQL string similar to the following :

strSQL = "SELECT TRANSACTION_DATE FROM FINANCIAL_TRANSACTION WHERE TRANSACTION_DATE > '14/12/2014'"

This gives the following run-time error :

Run-time error '-2147467259 (80004005)':
[Oracle][ODBC][Rdb]%SQL-F-DATCONERR, Data conversion error for string '14/12/2014'
-COSI-F-AMBDATTIM, ambiguous date-time

I am aware that the issue is due to the format of "TRANSACTION_DATE", and that I must express '14/12/2014' in the same format as "TRANSACTION_DATE" for my code to work. However, I am unsure how to find the exact format of "TRANSACTION_DATE".

The following SQL string does work :

strSQL = "SELECT MAX(TRANSACTION_DATE) FROM FINANCIAL_TRANSACTION"

The output is automatically converted by Excel to a Date format. If I change excels format to "General", the output is "41988". Now, the following SQL string gives the below error :

strSQL = "SELECT TRANSACTION_DATE FROM FINANCIAL_TRANSACTION WHERE TRANSACTION_DATE = 41988"

Run-time error '-2147467259 (80004005)':
[Oracle][ODBC][Rdb]%RDB-E-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-F-INV_DATE_CHG, invalid field datatype change to/from datetime

If I add quotation marks around the number, I get the data conversion error message mentioned earlier.

I'm now at a loss and am not sure where else to go. Has anyone else encountered this problem? What did you do to resolve the issue? Could anyone give any advise on how to improve my SQL string (in red), or offer an alternative to try?

Any help whatsoever would be greatly appreciated!

Thanks so much in advance.

Natalie

 

[par60056]

Excel recognizes date fields in databases and converts them to Excel dates. Excel dates are a number that represents days since 1/1/1900 with any decimal being the fraction of the day(or time).

Oracle usually expects dates to be in the format 'dd-mmm-yyyy' but it depends on how the database is set up.

I would try:
strSQL = "SELECT TRANSACTION_DATE FROM FINANCIAL_TRANSACTION WHERE TRANSACTION_DATE > '14-dec-2014'"

 

[nataliek92]

This works! I am new to Oracle, and have been trying various different date/time formats to try to match oracles expectation but to no avail.

Thanks so much!

 

[CharlesChuckieCharles]

try using 'dd-mmm-yyyy' since that is Oracles native form

 

[par60056]

Glad it helped.

I work with Excel accessing Oracle, Sybase and MS Access. They all do different things when trying to deal with dates.

 

[nataliek92]

Glad it helped.

I work with Excel accessing Oracle, Sybase and MS Access. They all do different things when trying to deal with dates.

Yes I have been made aware of this, but never known exactly what they all actually do with dates! Was unable to find anything on the internet to help.