From: reb01501@NOSPAMyahoo.com   
      
   rja.carnegie@gmail.com wrote:   
   > On Tuesday, September 4, 2012 4:25:26 PM UTC+1, Bob Barrows wrote:   
   >> justaguy wrote:   
   >>   
   >>> -- task: find records / rows where createddate is 8/23/2012   
   >>>   
   >>> select *   
   >>> from myTBL   
   >>> and datediff(dy,createddate,'8/23/2012') <1   
   >>>   
   >>> -- change parameter from dy to d does not help neither   
   >>> What's wrong?   
   >>>   
   >>> DBMS in question: MS SQL Server 2005 Express   
   >>>   
   >>> tia   
   >>   
   >> Why not simply   
   >>   
   >> WHERE CreatedDate='20120823'   
   >>   
   >> If times are being stored in CreatedDate, then it would be:   
   >>   
   >> WHERE CreatedDate >= '20120823' and CreatedDate < '20120824'   
   >>   
   >> These solutions both allow an index on CreatedDate to be used. Your   
   >> datediff solution forces a table scan.   
   >   
   > I haven't checked, does it?   
      
   Of course it does ... why do you need to check? The criterion is   
   non-sargable.   
   The only way an entire table scan would be avoided is if another indexed   
   field was being filtered with sargable criterion, thus allowing a subset of   
   the data to be scanned   
      
   > I'm sceptical that the DATEDIFF formula defeats SQL Server's query   
   > optimizer,   
      
   Ok go check it then.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)