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

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.

  • using Object_id() function with #tables
  • Given longitude and latitude of two areas, how to find the distance between them in meters. How to query in SQL..?
  • datediff rounding
  • why is t-sql allowing me to violate a check constraint that uses a UDP?
  • Comparing large number of big string text in SQL Server - Is Performance better if hash is compared instead of the strings
  • How to store geocoded address information into the database
  • I am looking for a way for a trigger to insert into a second table only where the value in table 1 changes
  • SQL Server 2008: TOP 10 and distinct together
  • SQL Query Error in Group By and Order By Clause
  • Rounding Datetime to nearest 15 minutes in SQL Server
  • How to find which views are using a certain table in SQL Server (2008)?
  • How would you implement sequences in Microsoft SQL Server?
  • 5 Solutions collect form web for “How do I find data from this day exactly one year ago?”

    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.

    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.
    

    If you have other fields that show as datetime with the time part as 00:00:00.000, to do joins you can use the below

     dateadd(yy,-1,datediff(d,0,getdate())) 
    

    Date today:

    select getdate()
    

    date one year ago:

    select dateadd(year, -1, getdate())
    

    Date one year and one day ago:

    select dateadd(d, -1 , dateadd(year, -1, getdate()))
    

    UPDATE:

    select *
    from
    epe_curremploymentdate = dateadd(year, -1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
    

    This will give you a list of all employees who started working exactly from a year ago.

    Hope this helps!!!

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.