CODEWITHSUNDEEP
mysqlsqldateselect
Mark Bogner
Mark Bogner

Reputation: 471

MySQL Select First Day of Year and Month

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() )

Upvotes: 21

Views: 43921

Answers (4)

Paul B.
Paul B.

Reputation: 1

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')

Upvotes: 0

Tim Bray
Tim Bray

Reputation: 1462

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.

Upvotes: 45

Grim...
Grim...

Reputation: 16953

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() )

Upvotes: 30

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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() )

Upvotes: 5

Related Questions