CODEWITHSUNDEEP
sql-server
mannj2018
mannj2018

Reputation: 83

How does one retrieve records 1 year prioir from specific date in sql server 2012

SQL Server 2012

I've created a temp table which contains date column with the following format yyyy/mm/dd

I'm looking for a way to pull records by date from the temp table that is -1 year from 2017/01/01. I'm aware of pulling -1 from current date using getdate, but I'm not certain on how to approach this using a date in the past.

Initially I was using the following:

      SELECT *
      from #participants_temp
  where ERSEFFDT < '2017-01-01'
  and ERSEFFDT <> '0000-00-00'

But I quickly realized that I have vast amount of records. I need to pull records exactly 1 year prior to 2017/01/01/

Any advice is much appreciated. Thank you.

Upvotes: 0

Views: 33

Answers (2)

Diana
Diana

Reputation: 1

declare @date date = '2017-01-01'

select * from #participants_temp where 
 ERSEFFDT between dateadd(yy, -1, @date) and @date

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try any of the below approaches

SELECT
    *
    FROM #participants_temp
        WHERE ERSEFFDT BETWEEN  '2016-01-01' and '2017-01-01'

or

SELECT
    *
    FROM #participants_temp
        WHERE ERSEFFDT BETWEEN DATEADD(YYYY,-1,'2017-01-01') and '2017-01-01'

Upvotes: 1

Related Questions