Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: [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 <français> 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!

  2. #2
    Join Date
    Sep 2003
    Posts
    16

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

    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

  3. #3
    Join Date
    Nov 2003
    Posts
    48
    Possible there is something to do with locale settings of windows, check the "Region Options" in Control Panel for both servers.
    Shianmiin

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    2

    Here is the solution

    (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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •