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

Question

Question

I would like to pull all assets from a database which is a certain amount of years old, in this case years. 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'

Answer 1

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

For an performance optimized query look at @Horaciuxs answer.

Answer 2

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())