CODEWITHSUNDEEP
sqlsql-servert-sql
SOF User
SOF User

Reputation: 7840

Months difference in SQL

Consider a datetime field Fld1.

How can I check whether this value is older than 3 months ago using a SQL query?

Upvotes: 3

Views: 9393

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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)

Upvotes: 9

Carlos Campderr&#243;s
Carlos Campderr&#243;s

Reputation: 23002

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 >=.

Upvotes: 0

Mike Marshall
Mike Marshall

Reputation: 7850

SQL Server:

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

Upvotes: 1

David
David

Reputation: 219097

(Assuming Microsoft SQL Server T-SQL):

Try the DateDiff function.

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

Upvotes: 0

Related Questions