CODEWITHSUNDEEP
mysqlsql
MAX POWER
MAX POWER

Reputation: 5448

MySQL - calculate start and end date of months

I have table months:

id   name
=============
1    January
2    February
3    March
..   ........

I have the year stored in a variable:

SET @year = YEAR(CURDATE());

I now want 2 new columns: start_date and end_date - both of these columns will contain the start date and end date of the month based on the id and @year variable. This will be in the standard MySQL date column format. Currently I have this:

CONCAT(@year, '-', LPAD(months.id, 2, '0'), '-', '01') AS start_date,
CONCAT(@year, '-', LPAD(months.id, 2, '0'), '-', '31') AS end_date

This does work but is there a better/cleaner way? Is there a way to automatically get the actual last day of the month?

Upvotes: 0

Views: 857

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can get the first day of the year using date() and strings. The rest can be done using date functions and operators:

select m.*,
       date(concat(year(curdate()), '-01-01')) + interval (id - 1) month as month_start,
       last_day(date(concat(year(curdate()), '-01-01')) + interval (id - 1) month) as month_end
from months m;

Here is a small db<>fiddle.

Upvotes: 2

Related Questions