July 15, 2010 at 3:09 am
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
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
July 15, 2010 at 3:31 am
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
No ,
the permanent solution is to use a language neutral format.
So 'YYYYMMDD HH:MM:SS'
July 15, 2010 at 3:42 am
Change the Default language setting, of the account used to run the queries, to "British English".
July 15, 2010 at 3:46 am
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
July 15, 2010 at 3:56 am
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
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