Sean Lange (1/23/2015)

---

Jeff Moden (1/23/2015)

---

Ed Wagner (1/23/2015)

---

Sean Lange (1/23/2015)

---

Ed Wagner (1/23/2015)

---

Sean Lange (1/23/2015)

---

Ed Wagner (1/23/2015)

---

sharonsql2013 (1/23/2015)

---

I have a column in the table with datatype DATE

Gives data in the format

YYYY_MM_DD

How can I go back to past 12 months in my where clause

So , Select * from XYZ

where Date_YYYY_MM_DD ......

To return the rows where date_column is later than 12 months ago:

SELECT columns_to_return

FROM dbo.XYZ

WHERE date_column > DATEADD(month, -12, GETDATE());

Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.

Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.

That is to say that the following two predicates are 100% the same thing.

WHERE date_column > DATEADD(month, -12, GETDATE());

OR

WHERE DATEADD(month, -12, GETDATE()) < date_column >

Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.

I know fully well you knew that just wanted to be clear in the answer. 😀

Absolutely. We don't want to lead someone down the wrong path. That's not why either of us are here.

Though it works, I find it easier to read and seems more logical when the column is on the left side of the "=" and the search criteria is on the right.

Agreed I find it rather obtuse when they are "backwards".