468,107 Members | 1,298 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

Is it a SQL Server 2000 bug

PFI
Hi, I'm currently using SQL Server 2000 SP3.
I'm facing some problems which make me think of a SQL Server BUG.

When executing a query, it seems that SQL Server tries to convert data
even if not in the resultset -> This leads to SQL Server error.

Here is a very easy sample to reproduce it :

SET NOCOUNT ON
create table tempdb..test ( coldate varchar(30) )

insert into tempdb..test values ( '1900/01/01 00:00:00' )
insert into tempdb..test values ( '2005/01/01 00:00:00' )
insert into tempdb..test values ( 'Invalid date' )

select * from tempdb..test
where isdate( coldate ) = 1
and convert( datetime, coldate ) > GETDATE()

SQL Server output is the following :

Server: Msg 241, Level 16, State 1, Line 10
Syntax error converting datetime from character string.

SQL Server considers as an error the 'Invalid date' even if a filter
ISDATE = 1 is applied....
I guess that I am not able to determine the order in which filters are
applied as it is SQL Server optimizer job... However, this should lead
to an error only if conversion fails on a line of the resultset after
application of all other filters....

What am I doing wrong ?

Thanks
Patrick

Jul 23 '05 #1
6 2264
Not a bug. The execution order is determined by the query optimizer.
There is no reason to suppose that the ISDATE expression will always
execute first.

What are you doing wrong? Firstly, you are writing queries against
dates stored as strings. If at all possible you should convert the
dates to use a proper DATETIME column. If you really cannot do that
then you should be able to rewrite your query using a derived table.

The second thing wrong here is that you are relying on an implicit
conversion from a non-standard date format. Those conversions are
sensitive to local server and connection settings so avoid them. Try
the following:

SELECT coldate
FROM
(SELECT REPLACE(REPLACE(coldate,'/','-'),' ','T') AS coldate
FROM tempdb..test
WHERE ISDATE(REPLACE(REPLACE(coldate,'/','-'),' ','T'))=1) AS T
WHERE CAST(coldate AS DATETIME) > CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same....
I guess that this is a special case due to the abuse of conversion
use...

Thanks
Patrick
*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #3
Hi, Patrick

For details about this problem and possible solutions, see this article
by Itzik Ben-Gan, SQL Server MVP:
http://www.windowsitpro.com/Windows/...48/pg/1/1.html

Razvan

Jul 23 '05 #4
No, an SQL Statement is a specification of the result, and
theoretically, it is created "all at once". It is not good enough that
you will get correct results if the query is executed "left to right,
top to bottom", because that is not how SQL works.

A solution for your problem could be to place the two related predicates
in a case expression. A case expression always has to be evaluated from
left to right.

select * from tempdb..test
where CASE WHEN isdate( coldate ) = 0 THEN 0
WHEN convert( datetime, coldate ) > GETDATE() THEN 1
ELSE 0 END = 1

Hope this helps,
Gert-Jan
Patrick Fiche wrote:

Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same....
I guess that this is a special case due to the abuse of conversion
use...

Thanks
Patrick

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #5
Here is another way:

select * from tempdb..test where case when isdate( coldate ) = 1
then convert( datetime, coldate ) else getdate() end > getdate()

Razvan

Jul 23 '05 #6
PFI
Thanks all for your help.

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Thue Tuxen Sørensen | last post: by
17 posts views Thread by Danieltbt05 | last post: by
14 posts views Thread by Developer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.