MySQL Select First Day of Year and Month

To get the first day of the current year, I use:

SELECT MAKEDATE(year(now()),1);

So in your query you would write:

where `date` >= MAKEDATE(year(now()),1)

I quite commonly do something like a sales report for the past full 2 years, but I always want to start at the beginning of a year. So shows 2 full years and the year to date.

where date>= MAKEDATE(year(now()-interval 2 year),1)

But to further complicate it, our financial years starts on the first of May. I always want to start on the first of May.

where date >= MAKEDATE(year(now()-interval 2 year),1) + interval 120 day

or as an alternative

where date >= MAKEDATE(year(now()-interval 2 year),121)

The first of May being the 121st day of a the year. But this method does not work in leap years.

The leap year proof version is:

where date => select MAKEDATE(year(now()-interval 5 year),1) + interval 4 month

Which will always return a xxxx-05-01 date, whether a leap year or not.

If the above works for the month, then this will work for the year:

WHERE (`DATE` between  DATE_FORMAT(NOW() ,'%Y-01-01') AND NOW() ) 

I think you need:

WHERE (`DATE` between  DATE_FORMAT(NOW() ,'%Y-01-01') AND NOW() ) 

To be honest, you could do:

WHERE (`DATE` between  DATE_FORMAT(NOW() ,'%Y') AND NOW() )