Sunday, March 08, 2009

Mysql: Find max and minimum date in a month

Recently I was looking for a quick way to find the first and last day of a  month, given any date from that month.
Michael Williamson seem to have a quick and fugly solution, which works quiet well.

Thanks Miachael.

Michael’s Blog » Blog Archive » MySQL number of days in a month
select date_add(concat(year(curdate()),'-',month(curdate()),'-','1'), interval 0 day) as mo_start,
date_sub(date_add(concat(year(curdate()),'-',month(curdate()),'-','1'), interval 1 month), interval 1 day) as mo_end;
Update: The same thing can be achieved by replacing curdate() with "now()". Here is the modified query.
select date_add(concat(year(now()),'-',month(now()),'-','1'), interval 0 day) as mo_start,
date_sub(date_add(concat(year(now()),'-',month(now()),'-','1'), interval 1 month), interval 1 day) as mo_end;

1 comment:

  1. Substitute your date for NOW():

    SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AS `first_day`, LAST_DAY(NOW()) AS `last_day`;

    | first_day | last_day |
    | 2009-03-01 | 2009-03-31 |