Month difference in SQL

Question

Consider the datetime field Fld1.

How to check if this value was older than 3 months ago using an SQL query?

Answer 1

From your other questions, it looks like SQL Server.

SQL Server 2 responses are already set to not SARGable (link) ; they cannot use indexes.

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

Create a date 3 months ago and test it; this will allow the use of indexes. This statement is true for any DBMS.

If you have passed the full calendar months, for example

• current date = February 24, 2011
• 3 months ago = November - 2010 (excluding the day of the month)
• required = any date in November 2010 and earlier

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

Answer 2

SQL Server:

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

Answer 3

(, Microsoft SQL Server T-SQL):

DateDiff.

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

Answer 4

MySQL

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

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