Query behaviour

Query behaviour

• TheWacoKid

  How about putting some constraints on your data so that you don't end up with garbage in your database

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <s_clarke@discussions.microsoft.com> wrote in message news:3f1071df-8eec-4da1-86bf-badc75fd0a1c@discussions.microsoft.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
  
  results
  IND SEQ  Message
  1      1    date=01/06/2006
  2      3    date=2/12/2005
  
  add ...
  
  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()
  
  the error occurs
  
  if I remove the row from table 'test'
  
  IND SEQ Message
  2      2   test
  
  then the above works!!!
  
  results
  IND SEQ  Message
  1      1    date=01/06/2006
  
  help!!
  

  
  

• LANewman

  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
  
  results
  IND SEQ  Message
  1      1    date=01/06/2006
  2      3    date=2/12/2005
  
  add ...
  
  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()
  
  the error occurs
  
  if I remove the row from table 'test'
  
  IND SEQ Message
  2      2   test
  
  then the above works!!!
  
  results
  IND SEQ  Message
  1      1    date=01/06/2006
  
  help!!
  
  

• Bruce Frost

  I think you're running into some locale-specific date formatting issues.  To avoid these problems, always use the ISO date format:

   

  YYYYMMDD

   

  So instead of 20/12/2005, use 20051220.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <s_clarke@discussions.microsoft.com> wrote in message news:349d4996-1295-45f4-acb4-58bd96e05631@discussions.microsoft.com...

  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
  
  1    date=01/06/2006
  1    date=20/12/2005
  
  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...
  
  1    date=01/06/2006
  1    date=20/12/2005
  
  
  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
  
  Simon

  
  

• ysr

  Unfortunately its not my database....
  
  I believe this query should work though...
  
  thanks
  
  Simon
  

• Farmer2

  Thanks that does the trick...
  
  thanks
  
  
  Simon
  

• Harley82

  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
  

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <s_clarke@discussions.microsoft.com> wrote in message news:dc023b2a-96c4-46e6-86fa-678b93f61536@discussions.microsoft.com...

  Unfortunately its not my database....
  
  I believe this query should work though...
  
  thanks
  
  Simon

  
  
• Query behaviour