CODEWITHSUNDEEP
mysqldate
Yashida Kim Lee Roger
Yashida Kim Lee Roger

Reputation: 105

Find Last Day of Last Year in MySQL

I know how to find this (current) year's last day in MySQL.

SELECT LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH)); //It shows this year (2017)'s last day. ==> 2017-12-31

But I do not know how to find LAST YEAR's Last Day.

What queries should I enter to determine last year last day?

For example:

++++This Year++++ | ++++Last Year's Last Day++++

   2017           |        2016-12-31
   2018           |        2017-12-31
   2019           |        2018-12-31
   2020           |        2019-12-31

Thanks for your help, much appreciated.

Upvotes: 0

Views: 2208

Answers (2)

gview
gview

Reputation: 15361

As I mentioned, in this particular case, where the last day of the year (or the first) never ever changes, you can go with a much simpler solution.

There are actually quite a few ways to do this, and in this case without making it into a datetime as Gordon did and using DATE_ADD with negative intervals. 

SELECT CONCAT(YEAR(CURDATE())-1,'-12-31');

+------------------------------------+
| CONCAT(YEAR(CURDATE())-1,'-12-31') |
+------------------------------------+
| 2016-12-31                         |
+------------------------------------+

Probably in most situations just getting that as a string in standard MySQL date format is fine, but if you actually need it to be a mysql date for comparison purposes or because the mysql client is reading the mysql datatype and doing something special with that in your serverside language, then you can convert that using STR_TO_DATE.

SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'), '%Y-%m-%d');
+-------------------------------------------------------------+
| STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'), '%Y-%m-%d') |
+-------------------------------------------------------------+
| 2016-12-31                                                  |
+-------------------------------------------------------------+

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269573

Just subtract one year:

SELECT DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH)), INTERVAL 1 YEAR)

Upvotes: 3

Related Questions