[quoted text, click to view] JayneK wrote:
> David,
>
> Unfortunately they all have all their language setting exactly the
> same. So they are all set to us_english as default language, yet one
> server acts differently to the other.
>
> How can we fix this? do we have to uninstall and re-install, can't
> we hack a file or something?
>
> "David Gugick" wrote:
>
>> JayneK wrote:
>>> Hi,
>>>
>>> We recently had a new environment created. The servers were all
>>> installed as separate instances on the same physical machine.
>>>
>>> For my first instance INST1
>>> When I execute the following query exec getMyData '1964-11-19'
>>> in query analyser everything is fine
>>> from my (ASP) website everything is fine
>>>
>>> For my second instance INST2
>>> in query analyser everything is fine
>>> from my asp website I get varchar cannot be converted to datetime.
>>>
>>> I can only believe that the default settings for the server were
>>> different when each of the SQL Server Installs were performed.
>>>
>>> I cannot change the way we pass dates in our website because it is a
>>> massive re-write of everytihg if I do.
>>>
>>> Is there some why of changing the default settings of the server
>>> after installing?
>>>
>>> I have tried using
>>> sp_configure
>>> SET language
>>> sp_defaultlanguage
>>>
>>> and all these methods did not fix my problem.
>>>
>>> Any ideas?
>>
>> When working with dates in character format, you should only ever
>> use a portable format. Two formats are supported that will never
>> cause problems related to the server's regional settings:
>>
>> yyyy-mm-ddThh:mm:ss.mmm (no spaces)
>> yyyymmdd
>>
>>
>> What is probably occurring is that one server is using MDY format and
>> the other is using DMY.
>>
>> For example:
>>
>> SET NOCOUNT ON
>>
>> SET DATEFORMAT MDY
>>
>> SELECT CAST('1964-11-19' as DATETIME)
>>
>> SET DATEFORMAT DMY
>>
>> SELECT CAST('1964-11-19' as DATETIME)
>>
>> -- Results
>> 1964-11-19 00:00:00.000
>>
>> Server: Msg 242, Level 16, State 3, Line 9
>> The conversion of a char data type to a datetime data type resulted
>> in an out-of-range datetime value.
>>
>>
>>
>>
>> --
>> David Gugick - SQL Server MVP
>> Quest Software
The ASP web site client is likely set up different. Go to that PC, open
up QA, and run the example above. The problem is that you are not using
a portable date format and are bound to run into these types of
problems. If you get rid of the hyphens in the date parameter, that will
probably fix the issue. Since you cannot easily change the code
executing the date procedure you wrote, why not just change the
procedure itself to strip the hyphens out using Set @MyDate =
REPLACE(@MyDate, '-', '')
--
David Gugick - SQL Server MVP
Quest Software
