SQL: How to find all results older than 1 year-CodePudding

I have this query:

SELECT * FROM table1 WHERE YEAR(datecolumn) = YEAR(DATEADD(YEAR,-1,GETDATE()))

From my understanding, this query will show everything in that table that is older than 1 year. I'm trying to get a list of items that were first stocked over a year ago. Is this the correct query to be running or could it be made easier?

I need the script to be able to run without a fixed date range. So say I run it today and it gives me 100 rows, if I run it tomorrow, because the script is looking at the date I run it, it might return a different result set.

CodePudding user response：

This will give you everything from last "year"

SELECT * 
FROM table1 
WHERE YEAR(datecolumn) < YEAR(GETDATE())

But you want it to change day by date. In that case do this

SELECT * 
FROM table1 
WHERE datecolumn < DATEADD(YEAR,-1,GETDATE())

CodePudding user response：

That query gives results for records of only the previous year.

To get records older than (today - 1 year) try this

SELECT * 
FROM table1
WHERE datecolumn < DATEADD(YEAR,-1,CAST(GETDATE() AS DATE))

And to get records before the current year

SELECT * 
FROM table1
WHERE datecolumn < DATEFROMPARTS(YEAR(GETDATE()),1,1)