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 !!

asked 22 Mar '10, 15:09

Justin%20Willey's gravatar image

Justin Willey
accept rate: 21%

edited 23 Mar '10, 01:47

Calvin%20Allen's gravatar image

Calvin Allen

Comment Text Removed

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]

(22 Mar '10, 19:29) Justin Willey
Comment Text Removed

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

(22 Mar '10, 19:38) Breck Carter

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

(25 Mar '10, 16:51) Justin Willey

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.

permanent link

answered 23 Mar '10, 22:46

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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

(25 Mar '10, 16:49) Justin Willey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Mar '10, 15:09

question was seen: 1,283 times

last updated: 06 Apr '10, 23:23