Groups > comp.databases.ms-sqlserver > #1264

Re: what's going on with datediff function?

From	"Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: what's going on with datediff function?
Date	2012-09-06 19:09 -0400
Organization	A noiseless patient Spider
Message-ID	<k2baft$job$1@dont-email.me> (permalink)
References	<aa23cac7-3e4f-4b12-83da-562fbc9c5d25@m18g2000yqp.googlegroups.com> <k256h5$gq9$1@dont-email.me> <6c01faa0-f5dd-40bd-8b7a-aae1259173ae@googlegroups.com> <k28kus$pca$1@dont-email.me> <fc27f26a-cee7-4d24-a471-7e77b2e62df5@googlegroups.com>

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

what's going on with datediff function? justaguy <lichunshen84@gmail.com> - 2012-08-30 12:34 -0700
  Re: what's going on with datediff function? Gene Wirchenko <genew@ocis.net> - 2012-08-30 14:04 -0700
  Re: what's going on with datediff function? "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-09-04 11:25 -0400
    Re: what's going on with datediff function? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 18:47 -0400
      Re: what's going on with datediff function? rja.carnegie@gmail.com - 2012-09-06 15:53 -0700
        Re: what's going on with datediff function? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-06 19:03 -0400
        Re: what's going on with datediff function? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-06 19:09 -0400
          Re: what's going on with datediff function? Gene Wirchenko <genew@ocis.net> - 2012-09-06 17:20 -0700
    Re: what's going on with datediff function? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-05 18:47 -0400
  Re: what's going on with datediff function? Lutz Uhlmann <news@invalid.invalid> - 2012-09-05 08:22 +0200

csiph-web