19

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() )
Mark Bogner
  • 451
  • 1
  • 5
  • 19

4 Answers4

42

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.

Tim Bray
  • 1,373
  • 10
  • 7
30

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() )
Grim...
  • 16,518
  • 7
  • 45
  • 61
5

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() )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Don't know why but in my case when browsing through 3 mln. records the fastest method is:

where `DATE` >= CONCAT(YEAR(NOW()),'-01-01')
Paul B.
  • 1