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

user_397674 0
Viewed 71685

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

date mysql
15 Answers
SELECT LAST_DAY(date) as last_date, DATE_FORMAT(date,'%Y-%m-01') AS fisrt_date FROM table_name

date=your column name

user_88730880

There are many ways to calculate the first day of a month, and the following are the performance in my computer (you may try this on your own computer)

And the winner is LAST_DAY(@D - interval 1 month) + interval 1 day

set @D=curdate();

select BENCHMARK(100000000, subdate(@D, (day(@D)-1))); -- 33 seconds
SELECT BENCHMARK(100000000, @D - INTERVAL (day(@D) - 1) DAY); -- 33 seconds
SELECT BENCHMARK(100000000, cast(DATE_FORMAT(@D, '%Y-%m-01') as date)); -- 29 seconds
SELECT BENCHMARK(100000000, LAST_DAY(@D - interval 1 month) + interval 1 day); -- 26 seconds
user_34302230

The solutions that use last_day() and then add/subtract a month and a day are not interchangeable.

Example:

date_sub(date_add(last_day(curdate()), interval 1 day), interval 3 month)

always works for any supplied number of months you want to go back

date_add(date_sub(last_day(now()), interval 3 month), interval 1 day)

will fail in some cases, for instance if your current month has 30 days and the month you're subtracting back to (and then adding a day) has 31.

user_168641380

This works fine for me.

 date(SUBDATE("Added Time", INTERVAL (day("Added Time") -1) day))

** replace "Added Time" with column name

Use Cases:

  1. If you want to reset all date fields except Month and Year.

  2. If you want to retain the column format as "date". (not as "text" or "number")

user_52391320

Slow (17s):

SELECT BENCHMARK(100000000, current_date - INTERVAL (day(current_date) - 1) DAY); 
SELECT BENCHMARK(100000000, cast(DATE_FORMAT(current_date, '%Y-%m-01') as date));

If you don't need a date type this is faster: Fast (6s):

SELECT BENCHMARK(100000000, DATE_FORMAT(CURDATE(), '%Y-%m-01'));
SELECT BENCHMARK(100000000, DATE_FORMAT(current_date, '%Y-%m-01'));
user_27617470
Related