Select Records for which the date field is spent a year

Question

I have a table with columns

 TypeExame, DateExame

How can I select all records with (Now-DateExame) > = 365 days?

Answer 1

select *
from MyTable
where datediff (day, DateExame, getdate()) >= 365

See DATEDIFF and GETDATE for further details.

Answer 2

If you have an index on DateExame, putting the condition like this will enable its usage:

SELECT *
FROM atable
WHERE DateExame <= DATEADD(DAY, -365, CAST(GETDATE() AS date))
;

The above references the date data type, which means you'd need at least SQL Server 2008 to run that. To make it work in earlier versions, you could rewrite the condition like this:

WHERE DateExame <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 365, 0)

Basically, the modified version uses the DATEADD/DATEDIFF method of truncating a datetime value with a minor tweak to also subtract 365 days along the way.

However, if all your DateExame values are dates without the time part, this simpler version should work just as well:

WHERE DateExame <= DATEADD(DAY, -365, GETDATE())

That is, removal of GETDATE()'s result's time part would be perfectly unnecessary.