Error 242 select with date conditions now failing

We have reinstalled SQL Server 2000 on a new server but select statements on
datetime fields are now failing where they didn't before. e.g.
SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null
or expiry_dt>='21/02/2005')
gives the error
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
It works fine without the quotes around the dates but we have tons of code
that assumes quotes round dates is needed. There must be a global setting
somewhere that needs set. Does anyone know which setting it is?
Thanks for any help.
BernieBernie
What is a datatype of your start_dt and expiry_dt columns ?
What does it give you?
SELECT * FROM CONTHEAD WHERE start_dt<='20050221' and (expiry_dt is null
or expiry_dt>='20050221')
"Bernie Beattie" <BernieBeattie@.discussions.microsoft.com> wrote in message
news:066DA44E-AAAE-4134-9AEB-BC6C6E761A64@.microsoft.com...
> We have reinstalled SQL Server 2000 on a new server but select statements
on
> datetime fields are now failing where they didn't before. e.g.
> SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null
> or expiry_dt>='21/02/2005')
> gives the error
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> It works fine without the quotes around the dates but we have tons of code
> that assumes quotes round dates is needed. There must be a global setting
> somewhere that needs set. Does anyone know which setting it is?
> Thanks for any help.
> Bernie|||This is why it pays to use the standard ISO format dates in code. Never rely
on implicit covnversions from regional date formats - the behaviour of those
conversions is too dependent on factors that you don't have complete control
over.
The following are the ISO standard forms for dates. These will work
regardless of server and connection settings:
'20050221' -- Just the date
'2005-02-21T17:59:00' -- Date/hours/minutes/seconds
'2005-02-21T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
The workaround for your situation is to set the default settings for
DATEFORMAT and LANGUAGE on your server. This will probably solve your
immediate problem but may not fix it permanently. The server settings are
only *default* settings so they can be overridden at the client. That's a
good thing because maybe not everyone will want the same settings but it
means that to make your code reliable you have to use the ISO format for dat
e
literals.
David Portas
SQL Server MVP
--|||Thanks for pointing me in the right direction David.
Our application that sends the select statements to SQL server can simply
send a set dateformat command when the ODBC connection is opened. That
should make all the application statements work just fine regardless of the
actual default setting on the server.
Bernie
"David Portas" wrote:

> This is why it pays to use the standard ISO format dates in code. Never re
ly
> on implicit covnversions from regional date formats - the behaviour of tho
se
> conversions is too dependent on factors that you don't have complete contr
ol
> over.
> The following are the ISO standard forms for dates. These will work
> regardless of server and connection settings:
> '20050221' -- Just the date
> '2005-02-21T17:59:00' -- Date/hours/minutes/seconds
> '2005-02-21T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> The workaround for your situation is to set the default settings for
> DATEFORMAT and LANGUAGE on your server. This will probably solve your
> immediate problem but may not fix it permanently. The server settings are
> only *default* settings so they can be overridden at the client. That's a
> good thing because maybe not everyone will want the same settings but it
> means that to make your code reliable you have to use the ISO format for d
ate
> literals.
> --
> David Portas
> SQL Server MVP
> --