How do I find data from this day exactly one year ago?

Question

Right now I have:

year(epe_curremploymentdate) = year(DATEADD(year, -1, SYSDATETIME())) 

But that is giving me everything from last year. All I want is the data from today's date, one year ago.

Answer 1

It should be:

epe_curremploymentdate = DATEADD(year, -1, GETDATE())

Don't check for year in your where clause.

EDIT:

Simple: use

cast(epe_curremploymentdate AS DATE) = cast(DATEADD(year, -1,
  GETDATE()) AS DATE)

That is, if you are using SQL Server 2008 and above.

Answer 2

This should get you to where you need to go:

Declare @StartDate datetime, @EndDate datetime

-- @StartDate is midnight on today's date, last year
set @StartDate = Convert(date, (DATEADD(year, -1, getdate()))) 
set @EndDate = DATEADD(Day, 1, @StartDate)

select * 
from YourTable 
where epe_curremploymentdate >= @StartDate 
    and epe_curremploymentdate < @EndDate

-- This where clause will get you everything that happened at any time 
-- on today's date last year.