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
date >= DATE_SUB(NOW(),INTERVAL 1 YEAR)
ORDER BY date;
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;