Received: by 10.224.111.8 with SMTP id q8mr2129051qap.3.1346972024783; Thu, 06 Sep 2012 15:53:44 -0700 (PDT) Received: by 10.52.180.202 with SMTP id dq10mr752708vdc.17.1346972024758; Thu, 06 Sep 2012 15:53:44 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!b19no1307640qas.0!news-out.google.com!da15ni851qab.0!nntp.google.com!b19no1307626qas.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Thu, 6 Sep 2012 15:53:44 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=92.40.227.145; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP NNTP-Posting-Host: 92.40.227.145 References: <6c01faa0-f5dd-40bd-8b7a-aae1259173ae@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: what's going on with datediff function? From: rja.carnegie@gmail.com Injection-Date: Thu, 06 Sep 2012 22:53:44 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: csiph.com comp.databases.ms-sqlserver:1262 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?