MySQL Select First Day of Year and Month

Question

How to find first day of year in SELECT?

SELECT `DATE`,`SomeValue1`,`SomeValue2`
FROM `SomeTableName`
WHERE (`DATE` >= [...first day of the year in date format...])

I found this for month - but I don't QUITE have the grasp enough for year: (I was looking for this for a separate query to find data between beginning of month and now)

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

Answer 1

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.