CODEWITHSUNDEEP
sql-server
Antony
Antony

Reputation: 83

Select Records for which the date field is spent a year

I have a table with columns

 TypeExame, DateExame

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

Upvotes: 3

Views: 78

Answers (3)

Andriy M
Andriy M

Reputation: 77657

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.

Upvotes: 3

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Try this one : 

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

By Using Datediff function you can subtract two dates . you can pass first argument as minute , day , month , year etc .

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39566

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

See DATEDIFF and GETDATE for further details.

Upvotes: 4

Related Questions