EJF EJF - 6 months ago 8
SQL Question

WHERE date BETWEEN (a year ago) AND (now)

I'm working on a query where I need to pull all results where the date is between now and a year ago. So if the query is run tomorrow, the results are going to be a little bit different than they were if it was run today. If I run it today, it would basically be like saying "where the date is between 6/1/2015 and 6/1/2016." Tomorrow, between 6/2/2015 and 6/2/2016, and so on.

I figured I could format it just the same as a regular

WHERE
clause involving dates. This is what I have:

WHERE CreateDate BETWEEN (DATEADD(Year,-1,GETDATE()) AND (GetDATE())


First, I'm not sure if the
DATEADD()
part is what I should use -- I just tried it based on what I was reading on another forum. Second, I've got a red squiggly telling me I've got a syntax error near
AND
.

What's the best way to go about this?

Answer

You can use DATEADD and it is aware of leap years so adding years to a date should be safe even if ran on a leap year (although I still prefer to stay extra safe and add days -- depends on how you want it to behave when ran on leap year).

The reason for the syntax error is your parenthesis. I count five staring but only four closing. I think you want

 BETWEEN DATEADD(Year,-1,GETDATE()) AND GetDATE()