ruben ruvalcaba (2/29/2008)
Hi,First of all thank you, it's a good point to be considered. In fact, I use functions on the WHERE clause very often, specially when filtering dates ... how do you optimize a query like this:
SELECT SUM(Amount) FROM myEntries WHERE YEAR(myEntries.myDate) = @Year AND MONTH(myEntries.myDate) <= @Month
Any clue? thank you
regards
Not positive nobody's replied yet... and just doing this off the cuff,...
Assuming @year and @month are parms...
Instead of @year-- declare @gedate and init as jan 1 of whatever @year is...
Instead of @month, declare @ltdate and init as the first of the month AFTER @month...
SO let's say you had @year=2008 and @month = 3
Set @gedate=1/1/2008 and @ltdate=4/1/2008. (easily derived from old parms)
Your WHERE turns to:
...WHERE myentries.mydate >= @gtdate AND myentries.mydate < @ltdate
EDIT
Ahh... as it turns out, I am third or fourth on the "response" list to this one. One thing I do prefer (and I *thought* I'd benchmarked it to confirm it causes no additional overhead) is to use
...WHERE foo >= @gedate and foo < @ltdate
rather than
...WHERE foo BETWEEN (@d1 and @d2) -- or is that "IS BETWEEN"; I never use this construct anyway...
I like to use the appropriately named GEdate (greater or equal) and LTdate (lessthan) so I don't get messed up based on the time portion of a (small)datetime. In environments I've worked in, sometimes we will zero out the time portion of a (small)datetime-- (Expiration Dates); in others (Phone Call Taken) we do not. If I write a routine I can use the logic above without worrying about whether a date is 3/3/2008 00:00:00 or 3/3/2008 23:59:59.890