MYSQL select data that is older then 2 years

Question

So I want to purge some data from the system which is older then 2 years.

I have a datetime field where I store.

I know I can do something like

SELECT * FROM `table` where date_field < (??? what goes here)

My problem is i think how do I calculate the date, or maybe I am confused

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add may help you. `DATE_ADD(date,INTERVAL expr unit)`, `DATE_SUB(date,INTERVAL expr unit`) along with `now()`. also: http://stackoverflow.com/questions/3887509/mysqls-now-1-day — xQbert, Mar 25 '16 at 15:25
Wouldn't you need to use > now-2 years? instead of < though? — xQbert, Mar 25 '16 at 15:28

xQbert · Answer 1 · 2016-03-25T15:36:36.600

5

For a specific answer in case the Manual isn't clear enough:

SELECT * FROM `table` where date_field < (now() - interval 2 year)

edited Mar 25 '16 at 15:36

answered Mar 25 '16 at 15:27

xQbert

score 4 · Answer 2 · answered Mar 25 '16 at 15:27

4

Use DATE_SUB

SELECT * FROM `table` 
where date_field < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)

answered Mar 25 '16 at 15:27

baao

2

Sure about that < ? don't you mean > ? – xQbert Mar 25 '16 at 15:29
2

Isn't he looking for a date prior to now - 2 years? So the dateField should be smaller than now -2 to match the correct data @xQbert – baao Mar 25 '16 at 15:31

score 3 · Answer 3 · answered Mar 25 '16 at 15:33

3

may this can help :

SELECT EXTRACT(YEAR FROM `date`) AS year 
from table 
having year< (EXTRACT(YEAR from CURDATE())-2)

answered Mar 25 '16 at 15:33

HichamEch

Panda · Accepted Answer · 2016-03-25T15:37:38.357

2

You can use INTERVAL:

SELECT * FROM `table` WHERE date_field < DATE_SUB(CURDATE(),INTERVAL 2 YEAR);

edited Mar 25 '16 at 15:37

answered Mar 25 '16 at 15:29

Panda

score 1 · Answer 5 · answered Mar 25 '16 at 15:35

1

SELECT * FROM table where date_field < DATE_SUB(NOW(),INTERVAL 2 YEAR);

answered Mar 25 '16 at 15:35

Tilak Raj

its simple, use now() function inside date_sub() – Tilak Raj Mar 25 '16 at 15:36

5 Answers5