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" Newsgroups: comp.databases.ms-sqlserver Subject: Re: what's going on with datediff function? Date: Thu, 6 Sep 2012 19:09:11 -0400 Organization: A noiseless patient Spider Lines: 32 Message-ID: References: <6c01faa0-f5dd-40bd-8b7a-aae1259173ae@googlegroups.com> Injection-Date: Thu, 6 Sep 2012 23:09:49 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="bd099084a2caba1d0ebe690afacb9523"; logging-data="20235"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+LPky0uVqUCSD0zWMO13LoLciFJk4+i4k=" 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:jYeVEkbonN+d2X+ZLnoAt0PhcQ8= X-Priority: 3 X-MSMail-Priority: Normal Xref: csiph.com comp.databases.ms-sqlserver:1264 rja.carnegie@gmail.com wrote: > On Wednesday, September 5, 2012 11:59:02 PM UTC+1, Bob Barrows wrote: >> rja.carnegie@gmail.com wrote: >> >>> On Tuesday, September 4, 2012 4:25:26 PM UTC+1, Bob Barrows wrote: >> >>>> 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. > > I have to concede that Wikipedia says so: > > "Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20 > Sargable: Select ... WHERE Date < DateAdd(mm,-20,GetDate())" > > Well, using GETDATE() might not help. > I think if you look at the execution plan, you'll find that it does.