Datetimes proxy tables and SQLServer

[justin_willey]

Hi

SQLA 10.0.1 & SQLServer 2005

Going round and round in circles trying to insert Datetimes into SQLServer.

The SQLA datataype is date and SQLServer is Datetime. The proxy table was created using the Sybase Central wizard which set the datatype to Timestamp. If you change the proxy table definition to anything else it gets changed straight back.

If you try to insert a row into the proxytable you get: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. " This seems to be a SQLServer error.

I can't work out why any type of conversion from text is involved, the data is types right through. We've tried all sorts of conversions, formatting, casting as anything and everything, messing with DATEFORMAT options etc, but it doesn't help - if you try passing through a string that SQLServer might like, SQLA right complains that the datatype is wrong. Googling the error produces lots of hits but no answers and some very odd suggestions like dataformats of YYYYDDMM

The YYYYDDMM clue does some shed light in that if the date is one that is ambigious (eg 5 May, 7 July, but not 17 Jan) it will work. If one trys to send an ambigious date with the month first, SQLA rejects it as an invalid date (and dateformat won't support YDM - unsuprisingly!). So there is a text conversion going on, but why?

All suggestions gratefully received !!

[justin_willey]

Got it to work by having date_order in SQLA as MDY and the language of the MSSQL user as us_english, but why on earth should these matter, surely a DATETIME is a DATETIME? It seems as though the ODBC driver is converting the SQLA DATETIME to text (in US format), which then has to be reconverted by MSSQL!! – Justin Willey 3 mins ago [delete this comment]

[Breck_Carter]

Show us the code... it's impossible to determine what's going on without the code 🙂

[justin_willey]

Hi, I'll give Glenn's suggestion a try but if that fails post a generic script that reproduces the problem. Thanks.

[Former Member · Accepted solution]

I don't know if this helps, but in February a customer commented on his inability to get certain things to work with proxy tables to a MS SQL Server 2005 instance, and the problem came down to the version of the Microsoft ODBC driver he was using - an upgrade to Service Pack 3 of MS SQL Server 2005 corrected the problem.

[justin_willey · Accepted solution]

Thanks - we'll give this a go, just trying MDAC first.