[mkreminski]

We installed the new ODBC driver on one of our servers today and one of our reporting applications stopped working.

After digging into the code and trying varios things we determine our code was failing when trying to perform a date operation.

The code worked in ODBC version 5.5

The prervious code was:

select * from TRANSACTIONS where TRANDATE >= ’05/05/2010′

We ran the code in both MSQuery and winSQL, it falied with both.

The results were:

WinSQL:

Error: Error getting the literal value of right operand.[0] (State:01000, Native Code: 0)

MSQuery:

Could not add the table. TRANSACTIONS

We modified the code to use another format.

select * from TRANSACTIONS where TRANDATE >= ‘2010-05-05’

This code works.

We now need to go back and check all of our code to determine if the date conversions we are using are correct.
This is a cached copy. Click here to see the original post.

[mhuffman]

Did you verify that your account date format preference (Setup>General Preferences) hasn’t been changed? If not, please file an issue and we’ll investigate, I wouldn’t expect you to have to make this change just by upgrading to the 6.0 driver.

Update: Turns out we recently investigated this (issue 178473)- the explanation from our ODBC vendor is that they standardized the date format due to errors for dates earlier than the year 0059 (I know that sounds a bit strange!) so what they call ‘nonstandard’ date formats like MM/DD/YYYY are no longer supported in 6.0. We will add this to our ODBC/upgrade documentation, I apologize that it was not previously communicated.

[mstalker]

RE: ODBC Driver 6.0 Date Conversion Errors

For anyone wondering, the standard format is yyyy-mm-dd

See http://msdn.microsoft.com/en-us/libr…v=sql.90).aspx for more details.

-Michael

[Author]

Posts