Query behaviour

Hi there,
I wonder if one of you worthy folks can help me out with some strange behaviour exhibited by a piece of SQL. Its my first post here , so please be gentle. :)

Here is my simple example :-

<my test table>

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')

insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

The first query is

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )

fine... 2 rows

second query

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

fine same 2 rows...

but If I try to combine the 2 clauses in

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

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

Please can anyone help me on this?

thanks

Simonproblem is you try to convert MESSAGE (varchar) to datetime datatype but you have 'test' inserted in your table
so 'test' string can not be converted to datetime datatype

select SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) from test

01/06/2006
20/12/2005
test

then this fails:

select convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) from test

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

thanks for the prompt reply. I've already considered this...

the second query eliminates the bad data row...

select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()

The nested query in query 1 is trying to do the same thing - but doesn't work for some reason

thanks

Simon|||Hmmm,
I played with it but it's mystery to me. Who can explain this:

table and data:

create table test
(ind int,
message varchar(255))

insert into test (ind, message) values
(1,'date=01/06/2006')
insert into test (ind, message) values
(1,'date=20/12/2005')
insert into test (ind, message) values
(2,'test')

now I have 2 statements which returns the same data:

select *
from test
where message like 'date=%'

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

when I use first select as subquery (temp table) it runs fine:

select *
from
(
select *
from test
where message like 'date=%'
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

ind message
--------
1 date=01/06/2006
1 date=20/12/2005

when I use secnd select as subquery it fails:

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

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

problem is in where clause:

...
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103) > getdate()

but why?|||her we go----

select *
from test t1
where
message like 'date=%'
and t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()|||that's nice but you missed my point. it worked also with 3rd select I posted. What I'd like to know is why 4th statement fails if select statement is the same:

select * from
(
...
) where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

only difference (comparing to 3rd) is subquery. but it returns the same result... for both (3rd and 4th statement)
so I'd say: I run same query against same data but result is not the same.|||see this two codes first

--this will work
select *
from test t1
where
message like 'date=%'
and t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()

--this will not work
select *
from test t1
where
t1.ind=(select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%' )
and CONVERT(datetime,(SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)),103)>getdate()
and message like 'date=%'


the reason is sql engine filter data based on first condition(ie messege like 'date=%') then it process 'convert' clause in the where clause

Insecond case it doing the opposite|||ok so what's happening in this statement?

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

I assume first of all it convert Message to datetime. But it should convert just
01/06/2006
20/12/2005
as subquery returns just those two records. there's no reason for failure or am I wrong?|||ok so what's happening in this statement?

select *
from
(
select * from test t1
where t1.ind in (select max(ind) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%')
) test
where convert(datetime, SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10) ,103)

I assume first of all it convert Message to datetime. But it should convert just
01/06/2006


20/12/2005
as subquery returns just those two records. there's no reason for failure or am I wrong?


take the execution plan of that query(Ctrl+L) and see the Argument: in table scan.U can see how sql server query is processing.

I welcome more comments from SQL server gurus|||I finally found the answer to this. I got it from MS (via MSDN)

"The query engine is free to evaluate predicates in whatever order it deems fit. If you need to control the order, you can do so via a CASE expression (but this may slow things down a bit)... try:

select * from (
select * from test t1
where t1.seq in (select max(seq) from test t2
where t2.ind = t1.ind
and t2.message like 'date=%'
group by t2.ind)) test
where
CASE WHEN message like 'date=%' THEN
CASE WHEN convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
THEN 1
ELSE 0
END
ELSE 0
END = 1"

While not a solution as such a good work around. The key to the above is query predicates - the workaround forces this. So, good enough for me.

thanks

Simon

Hope this helps others out :)