Path: csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: "Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: what's going on with datediff function?
Date: Wed, 5 Sep 2012 18:47:42 -0400
Organization: A noiseless patient Spider
Lines: 42
Message-ID: <k28kus$pca$1@dont-email.me>
References: <aa23cac7-3e4f-4b12-83da-562fbc9c5d25@m18g2000yqp.googlegroups.com> <k256h5$gq9$1@dont-email.me> <6c01faa0-f5dd-40bd-8b7a-aae1259173ae@googlegroups.com>
Injection-Date: Wed, 5 Sep 2012 22:50:04 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="bd099084a2caba1d0ebe690afacb9523"; logging-data="25994"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18RlJxO6KPwOs3PJ1WKKuHy3RY9RGan4uY="
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646: Format=Flowed; Original
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
Cancel-Lock: sha1:q+Zsuua/F00iVeYfTPqwaJnfoco=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: csiph.com comp.databases.ms-sqlserver:1254

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.