Display record older than 3 months in sql

Question

I need to create a SSIS package that will go through the records in one table(T1) older than 3 months (based on ALERT_TIMESTAMP) and move them to another table(T2)

My query is :

SELECT * FROM T1
WHERE (DATEDIFF([month], ALERT_TIMESTAMP, GETDATE()) > 3)

Alert_timestamp column is in Datetime format. eg: '10/26/2012 12:00:00 AM'

When I run the query it should display all the records that are older than 3 months, but it does not.

Answer 1

Try this

select * from `table` where `yourfield` >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)

For days, year see below for example.

DATE_SUB(CURDATE(), INTERVAL 15 DAY) /*For getting record specific days*/

DATE_SUB(CURDATE(), INTERVAL 1 YEAR) /*for getting records specific years*/

---

For Anand, query

BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE() ,INTERVAL 3 MONTH ) 
/* For Getting records between last 6 month to last 3 month

Answer 2

What you posted is not MySQL. Assuming you are using MS SQL Server, you should use the ABS() function.

SELECT   * FROM        T1
WHERE     ABS(DATEDIFF([month], ALERT_TIMESTAMP, GETDATE())) > 3