arpit1611 arpit1611 - 5 months ago 8
MySQL Question

Get data for last one year from database

I want to get data records for last one year which is ordered by date. But problem is that I need the data starting from first day of that month of past year.

For example, if its 15-June-2016 today, then I need to get data from 1-June-2015.

I tried to do it with

SELECT *
FROM Table
WHERE
date >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
ORDER BY date;


but Its not working.

Answer

Try using DATE_FORMAT to force it to be the first day of the month :

SELECT *
FROM Table t
WHERE
   t.date >= DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 YEAR),'%Y-%m-01')
ORDER BY date;