Alex Rivera | Logout

How to get first day of every corresponding month in mysql?

Asked 2010-07-21T10:34:39.757
58

I want to get first day of every corresponding month of current year. For example, if user selects '2010-06-15', query demands to run from '2010-06-01' instead of '2010-06-15'.

Please help me how to calculate first day from selected date. Currently, I am trying to get desirable using following mysql select query:

Select
  DAYOFMONTH(hrm_attendanceregister.Date) >=
  DAYOFMONTH(
    DATE_SUB('2010-07-17', INTERVAL - DAYOFMONTH('2010-07-17') + 1 DAY
  )
FROM
  hrm_attendanceregister;

Thanks

mysql date
Edit
Report

2 Answers

43

You can use the LAST_DAY function provided by MySQL to retrieve the last day of any month, that's easy:

SELECT LAST_DAY('2010-06-15');

Will return:

2010-06-30

Unfortunately, MySQL does not provide any FIRST_DAY function to retrieve the first day of a month (not sure why). But given the last day, you can add a day and subtract a month to get the first day. Thus you can define a custom function:

DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;

That way:

SELECT FIRST_DAY('2010-06-15');

Will return:

2010-06-01
answered 2012-06-22T14:45:16.130
-1
select big.* from
(select @date := '2010-06-15')var
straight_join 
(select * from your_table where date_column >= concat(year(@date),'-',month(@date),'-01'))big;

This will not create a full table scan.

answered 2010-07-21T10:52:34.220

Your Answer