Finding Dates

Derik Hammer shows the right way and the wrong way of using date functions in a WHERE clause:

I then changed my thought process to find the age of a 65 year old who’s birth day is today. Then I compare the DateOfBirth column to that static value.

SELECT COUNT(*) FROM dbo.Person p WHERE DateOfBirth <= DATEADD(YEAR,-65,GETDATE())

With the above query I bought myself an index seek and 345 logical reads. That works out to <3% of the cost.

This is true not just for date functions, but rather is applicable to almost all scalar functions.

Related Posts

Using DATEPART In SQL Server

Randolph West shares some thoughts on the DATEPART function: As we learned some time ago, an INT (integer) in T-SQL uses four bytes, and has a maximum value greater than zero of over 2 billion (there are more than 4 billion values in an integer if we take the negative values into account). Why then are date and time parts […]

Read More

Removing Time From A DateTime

Wayne Sheffield compares the performance of four methods for removing time from a DateTime data type: Today, we’ll compare 3 other methods to the DATEADD/DATEDIFF method: Taking advantage of the fact that a datetime datatype is stored as a float, with the decimal being fractions of a day and the whole numbers being days, we […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930