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