jaynek%¬§x§b±Ë¬²*²hœ®‹(~×(

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 ****yser everything is fine
from my (ASP) website everything is fine

For my second instance INST2
in query ****yser 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?

david gugick

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

jaynek%¬§x§b±Ë¬²*²hœ®‹(~×(

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

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