SQL and ISO date problems
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  [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:
'23 Jan 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?