Post reply

Date difference between 2 versions of SQL Server 2k5

  • July 15, 2010 at 3:09 am

    #139791

    On one system 32 Bit system I inherited this code ran without issue;

    select DATEADD(day, 1, '15/7/2010 9:39:17:42')

    Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    However on a new 64 bit system using the following build it fails with this error;

    Microsoft SQL Server 2005 - 9.00.4230.00 (X64) Jul 30 2009 13:42:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

    Why is this occuring between the two systems? I know what the SQL error referrers to but I don't want to change the code without understanding the underlying differences between the systems.

    Thanks

  • July 15, 2010 at 3:19 am

    #1193313

    Its to do with how your client connects to the server.

    The date format looks like its changing from DDMM to MMDD.

    Try this page http://www.karaszi.com/SQLServer/info_datetime.asp


    Clear Sky SQL
    My Blog[/url]

  • July 15, 2010 at 3:31 am

    #1193316

    Yes, you 100% correct.

    So short of changing the application by putting

    set language british at the start of each query what would be the permanent solution?

  • July 15, 2010 at 3:41 am

    #1193320

    No ,

    the permanent solution is to use a language neutral format.

    So 'YYYYMMDD HH:MM:SS'


    Clear Sky SQL
    My Blog[/url]

  • July 15, 2010 at 3:42 am

    #1193321

    Change the Default language setting, of the account used to run the queries, to "British English".

  • July 15, 2010 at 3:46 am

    #1193322

    Yes it would have been much easier if however built the app using a date neutral format.

    Changing the user language makes no difference I'm afraid.

    Judging by this thread it seems very hard to change the language of the server install

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891

  • July 15, 2010 at 3:56 am

    #1193328

    It made no difference? Hmmm... It should do. I've just run exactly your query with the language set to each of the two options, English or British English, to make sure and it worked. You do have to disconnect the account from the database and then reconnect for the change to take effect. Thereafter all dates should be interpretted as British dates when the account runs a query, without the need to use the set language T SQL.

  • July 15, 2010 at 4:00 am

    #1193329

    OK I'll give it a go shortly. Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply