Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: what's going on with datediff function?

Show all headers | View raw

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.

And yet... well, I always forget how DATEDIFF works anyway 
(e.g. 2012-09-05 23:59 vs. 2012-09-06 00:01, 2 minutes, 1 day??)
and I don't have a server to remind myself, but this looks to me like 
a case where you or I can fairly easily manipulate the statement
to produce something that /will/ use an index efficiently, 
and so the server itself ought to do that, too.  I mean to say,
this software ain't cheap.  But if it doesn't, then it doesn't.

I'm fussy enough to assign GETDATE() into a variable @now in a 
procedure and use that, instead, in case the clock ticks forward
before the thing finishes.  In some use-cases, hitting midnight 
could be particularly embarrassing.

Whereas our Java-based overnight task scheduler activates jobs at 
run-time according to day-of-week and /always/ runs through midnight - 
I hope - so I have to admit that my employer doesn't have a 
strong anti-idiot policy.  Now excuse me while I go look in 
a mirror.

Come to think, I wonder if they have a watertight policy against
murdering colleagues.  There's probably something about responsibility
for accidents, but what if it isn't an accident?

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