[strange] date format issue: 2 servers, one query, 2 different results.

I'm running 2 WIN2K SP4 servers with MSSQL Server (v 7.00.1077). Both NT servers seems to have the same setup. I use the ADODB object to connect to MSSQL via OLE DB and notice that the following query generates an error on one server, not the other:

SELECT DATA FROM TABLE_1 WHERE ((TRANSACTION_DATE >='13/11/2003') AND (TRANSACTIONS_DATE <='20/11/2003')) [dates are DD/MM/YYYY]

Query on server_1:
- using OLE DB: correct
- using ODBC: correct
- using Query Analyzer: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>

On server_2:
- using OLE DB: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>
- using ODBC: correct
- using Query Analyzer: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>

Both servers have MDAC 2.8 RTM installed and a <select @.@.language> returns <us_english> via query analyzer and <franais> via ODBC.

I'd like to setup Server 2 so that queries can be executed the same as on server 1.

Any ideas are welcomed!There's a problem with 20/11/2003 in us_english format. Default is: MDY, so it's reading 20th as month, which is why you're getting that error.

Here's an article I found: http://members.lycos.nl/digispy/C2/P56/C2P56A1806.htm

G'luck!
Meera|||Possible there is something to do with locale settings of windows, check the "Region Options" in Control Panel for both servers.|||I'd say the languages are different...and accordingingly each date format is different

Di SELECT * FROM master..syslanguages

I think English is the only one that mdy|||(D/M/Y is a French date format).

I just had to set <French> to user's default language instead of <US_ENGLISH>. This means ODBC settings override the default SQL Server settings as OLE DB uses the default SQL Server setting.sql