Hi - This is my first posting to this forum,
  I'm having problems on just one particular machine. OS is Win 2k, SQL Server 7 SP3. I'm having problems on this machine when inserting an ISO date into a datetime field. I've built a noddy application to demonstrate the problem. The app is VB6 using ADO to talk via a DSN to SQL Serve 7 DB (SQLSVR32.dll). The third parameter in the SQL statement goes into the datetime field. The SQL statement is:

"INSERT INTO CRELQUERIES VALUES( 5, 'Payments/Repayments - July 2002', '2003/01/23', 'TP', 'Services', '', '', '', '', '', '', 'http://www.clientrelate.cch.co.uk/index.asp?pt=detailsservices&pi=crs000010', 'Amount,,', '1', 0, '1.1', 2)"

This generate the error : DIAG [22008] [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (242)

This only occurs one one of my machines - all the rest appear to work OK.

By using an ISO format date, I should be bypassing all of the local date translation mechanisms, but it doesn't appear to work.

I've written a testbed to nobble the date in the SQL statement to try multiple formats. These are the results:

Format Result
----------------------
'2003/01/23' Fail
'2003-01-23' Fail
'01/23/2003' Fail
'23 Jan 2003' Success
'23/01/2003' Success

The dates in SQL server are all defaulted to be US English. The regional settings on the machine are set up to be British English (not that these should matter).

Can anyone see where I'm going wrong?

Rgrds
  Callum

Hi.

It is wise to use the unseparated string format when inserting datetimes. This format should work no matter what dateformat the server or client is set up with.

Try '20030123' and se if that will work...

Gert

How do you use the unseparated string format to insert or update datetimes with both a date and a time part (eg how do you insert 25th Jan 2006 09:27)?

I've found out - it's '20060125 09:27'

I would suggest you use the convert function and not just the insert as your posted.

I have a very similar problem.

Two servers one in VMWARE the other a 'real' server.

They have same OS - Sql Server 2000 sp4 with the same regional and locale settings throughout.

Same code base and same database - files detached and copies attached to the other.

One 'real' accepts dates int format '2007-07-01 19:16:34' the other gives

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (242)


With the same regional and local settings and with a date that cannot be interpreted as being anything other than in 2007 and valid even if the month and day are reversed, how can this error be produced.

The code and database has been deployed to many other servers in the past without a problem.

Any help appreciated.

Regards Kevin.

Try this function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164