Different Default Date format for 2 SQL Server Instances

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?
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
|||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:
> 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 wrote:[vbcol=seagreen]
> 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:
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