The following query works fine against SQL Server 2000 here in the US:

SELECT * from TNEWSARTICLES where CreatedOn < '2003-04-25 14:22'

But in the UK, it returns this error:

80040e07: The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value

If I change the format to YYYY-DD-MM, then it does work in the UK. But
I was under the impression that the YYYY-MM-DD format will work
regardless of regional date format settings. Is this wrong?

Any help is appreciated
Ted

Ted Johnson (t.google (AT) wjohnson1 (DOT) com) writes:
Yes. YYYY-MM-DD may work well in ODBC regardless of regional settings.
But in SQL Server the safe format is YYYYMMDD. You can also use
YYYY-MM-DDTHH:MM:SS where T stands for itself. YYYY-MM-DD is not safe
in SQL Server.


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks, I was (wrongly) assuming the the time portion could be omitted
while using YYYY-MM-DD.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Erland Sommarskog <sommar (AT) algonet (DOT) se> wrote

You could also use convert(varchar(8), CreatedOn, 112) to omit the
time part and return the date in the format 'YYYYMMDD'. This saves
the > and < functions when searching for dates.

Hodge