YYYY-MM-DD not working in UK

[Ted Johnson]

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

[Erland Sommarskog]

Ted Johnson (t.******@wjohnson1.com) writes:

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?

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, so****@algonet.se

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

[Ted Johnson]

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!

[Tony Hodgson]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

Ted Johnson (t.******@wjohnson1.com) writes:

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?

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.

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