CODEWITHSUNDEEP
sqlsql-server
Dan Cundy
Dan Cundy

Reputation: 2849

SQL Server: How to get rows where the date is older than X years?

Question

I would like to pull all assets from a database which is a certain amount of years old. Is this statement correct?

Background

The database is called AssetRegister

The table is called dbo.Assets

the column is called AcquiredDate

Statement so far

SELECT * FROM dbo.Assets WHERE AcquiredDate < '2008-01-01'

Upvotes: 12

Views: 59890

Answers (5)

SinisterPenguin
SinisterPenguin

Reputation: 1618

CAREFUL - DATEDIFF only looks at year. Consider this query:

SELECT DATEDIFF(year,'2012-10-01','2022-06-01')

clearly the date is 9 years 8 months old but the query returns 10. So if you are deleting records >= 10 years old based on this kind of query you're in a fix....

enter image description here

Better to use something like this as highlighted by @Horaciux

WHERE dt > DATEADD(YEAR,-10,'20220501')

Upvotes: 0

juergen d
juergen d

Reputation: 204746

SELECT * FROM dbo.Assets                  
WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) >= 8

For an performance optimized query look at @Horaciuxs answer.

Upvotes: 25

Horaciux
Horaciux

Reputation: 6477

The answer by @Juergen bring the right results:

SELECT * FROM dbo.Assets                  
WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) >= 8

But, the SQL optimizer can't use an index on AcquiredDate, even if one exists. It will literally have to evaluate this function for every row of the table.

For big tables is recommended to use:

DECLARE @limitDate Date
SELECT @limitDate=DATEADD(year,-8,GETDATE()) --Calculate limit date 8 year before now.

SELECT * FROM dbo.Assets                  
WHERE AcquiredDate <= @limitDate

Or simply:

SELECT * FROM dbo.Assets                  
    WHERE AcquiredDate <= DATEADD(year,-8,GETDATE())

Upvotes: 15

Matt
Matt

Reputation: 15061

SELECT * FROM dbo.Assets WHERE AcquiredDate >= '2006-01-01'

or

SELECT * FROM dbo.Assets WHERE AcquiredDate >= (year(getdate()) - 8)

Upvotes: 2

CiucaS
CiucaS

Reputation: 2128

SELECT * FROM dbo.Assets 
 WHERE YEAR(AcquiredDate) < 2008

The solution posted by juergen d and Ankit are better, but if you want to compare the Year, you can use the YEAR function.

Upvotes: -2

Related Questions