CODEWITHSUNDEEP
sqlsql-server
Dan Cundy
Dan Cundy

Reputation: 2849

Expressing age between two years

Question

How could I express the following statement in my query?

Between 4 and 5 years old

SQL Query

WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) <=2

Get all Assets that are more than one year old from todays date

What I want to say?

Get all assets between 4 and 5 years old

Upvotes: 2

Views: 79

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

Be very careful using datediff(year). It counts the number of year boundaries between two dates. So, the difference between 2014-12-31 and 2015-01-01 is 1.

In addition, I recommend putting the functions on the getdate() value rather than on the column. This allows an index to still be used on the column ("sargability"). So, something like this should do what you want:

where AcquiredDate >= dateadd(year, -5, GetDate()) and
      AcquiredDate < dateadd(year , -3, GetDate())

On 2015-01-01, this will retrieve rows acquired between 2010-01-1 and 2011-12-31, which seems to be the intent of the question.

Upvotes: 4

Matt
Matt

Reputation: 15061

Use BETWEEN http://www.w3schools.com/sql/sql_between.asp

SELECT *
FROM table
WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) BETWEEN 4 and 5

Upvotes: 0

Rhythem Aggarwal
Rhythem Aggarwal

Reputation: 356

check for SQL's BETWEEN OPERATOR here SQL Between

SELECT * FROM TABLE NAME WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) BETWEEN 4 and 5

Upvotes: 3

Related Questions