Months difference in SQL

Consider a datetime field Fld1.

How开发者_StackOverflow社区 can I check whether this value is older than 3 months ago using a SQL query?

---

From your other questions, it looks like SQL Server.

The 2 SQL Server answers already given are not SARGable (link); they cannot utilize indexes.

WHERE datecolumn < DATEADD(month, -3, GETDATE())

Construct a date 3 months ago and test against it; this will be able to use indexes. This statement holds true for any DBMS.

If you are after full calendar months, e.g.

• current date = 24-Feb-2011
• 3 months ago = Nov - 2010 (ignoring day of month)
• required = any date in Nov-2010 and earlier

WHERE datecolumn <= DATEADD(month, datediff(month, 0, getdate()) -2, 0)

---

SQL Server:

select * from table where DATEDIFF(m, dateColumn, GETDATE()) < 3

---

(Assuming Microsoft SQL Server T-SQL):

Try the DateDiff function.

... WHERE DATEDIFF(month, Fld1, GETDATE()) >= 3 ...

---

MySQL

SELECT * FROM table WHERE Fld1 <= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)

This will select elements older than 3 months. If you want elements newer than three months ago, just change <= to >=.