Query behaviour ?

Need some help from you on the following query behaviour:

1).
select Round(convert(float,40000.01),2)
----------------
40000.010000000002

select convert(nvarchar(25),40000.01)
--------
40000.01

2).
select convert(nvarchar(25),Round(convert(float,40000.01) ,2))
--------
40000

Questions:
1. Why does the second query round up the result to an integer ??
2. Why does the first query insert a 2 at the 12th decimal ??
3. Why the results of the two queries are different?

RgdsThe problem is is that you are using float. Go to sql server books online and look at the article "Using decimal, float, and real Data".

Query behaviour - (subquery results)

Hi there,
I'm experiencing some very strange effects when I trying to include a
subquery. I shall demonstrate with a simple example...
create table test
(ind int,
seq int,
message varchar(255))
insert into test (ind,seq, message) values
(1,1,'date=01/06/2006')
insert into test (ind,seq, message) values
(2,1,'date=1/12/2005')
insert into test (ind,seq, message) values
(2,2,'test')
insert into test (ind,seq, message) values
(2,3,'date=2/12/2005')
The column IND is theoretically a foreign key, the SEQ is a primary key. A
quick explanation is that this is a comment table from a main table (main
table being an 'order' table and this being a 'order comment' table.. the
relationship being (order) 1:m (comment) But for this example this doesn't
really matter.
So here are 2 queries.
select * from test t1
where t1.ind =1
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This one simple extracts the date from the text string. It works OK. I've
had to include the IND =1 to avoid the date conversion error. It just shows
that the conversion works.
results
IND SEQ Message
1 1 date=01/06/2006
The second query...
select max(seq) from test t2
where t2.message like 'date=%'
group by ind
This is extrating the highest 'SEQ' for each 'IND'. ie the last comment
(that has got a date component) for each order
results
SEQ
1
3
So thats OK.
Now the fun starts when I try to combine the two...
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)
where convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
This causes a
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'.
So, its implying the date format is incorrect. If I remove the convert :-
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)
then the results are OK -
IND SEQ Message
1 1 date=01/06/2006
2 3 date=2/12/2005
Any help please?
thanks
Simon(...)
--Replace the where with and AND -->
AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
HTH, jens Suessmeyer.|||Sorry my bad typo... I meant AND... The query should have been
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)
and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
this results in the error. This isn't just a syntax error.
Thanks though
"Jens" wrote:

> (...)
> --Replace the where with and AND -->
> AND convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> HTH, jens Suessmeyer.
>|||A flaw in the substring function:
...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
Maybe that's it.
ML
ML
http://milambda.blogspot.com/|||I don't think this is it...
If I do a
select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
from test
I get the results..
IND SEQ converted date
1 1 01/06/2006
2 1 1/12/2005
2 2 test
2 3 2/12/2005
So this shows that 3 of the rows can be converted into datetime (103 style).
I think that the problem is that the convert is being done on the whole data
set before approriate rows are excluded.
If I turn this into an inline view then I get the same error.
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 convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
10)),103) > getdate()
<bte this is where my original typo came from ;-) >
help !
"ML" wrote:

> A flaw in the substring function:
> ...(SUBSTRING(Message, CHARINDEX('=',Message) + 1, 10)),103) > getdate()
> Maybe that's it.
>
> ML
>
> ML
> --
> http://milambda.blogspot.com/|||I've had a quick look at this and get the same odd result.
The Where clause is being applied to all the contents of the input table.
You can see this in the showplan and can confirm it by removing the row
without a date.
Regards,
Craig
"s_clarke" wrote:
> I don't think this is it...
> If I do a
> select ind , seq, SUBSTRING(message, CHARINDEX('=',message,0)+1, 10)
> from test
> I get the results..
> IND SEQ converted date
> 1 1 01/06/2006
> 2 1 1/12/2005
> 2 2 test
> 2 3 2/12/2005
>
> So this shows that 3 of the rows can be converted into datetime (103 style
).
> I think that the problem is that the convert is being done on the whole da
ta
> set before approriate rows are excluded.
> If I turn this into an inline view then I get the same error.
> 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 convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1,
> 10)),103) > getdate()
> <bte this is where my original typo came from ;-) >
> help !
>
> "ML" wrote:
>

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 :)