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

Question

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

Answer 1

Is this what you are looking for:

select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);

Answer 2

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

Answer 3

There is actually a straightforward solution since the first day of the month is simply today - (day_of_month_in_today - 1):

select now() - interval (day(now())-1) day

Contrast that with the other methods which are extremely roundabout and indirect.

---

Also, since we are not interested in the time component, curdate() is a better (and faster) function than now(). We can also take advantage of subdate()'s 2-arity overload since that is more performant than using interval. So a better solution is:

select subdate(curdate(), (day(curdate())-1))

Answer 4

This is old but this might be helpful for new human web crawlers XD

For the first day of the current month you can use:

SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY;

Answer 5

You can use EXTRACT to get the date parts you want:

EXTRACT( YEAR_MONTH FROM DATE('2011-09-28') )
-- 201109

This works well for grouping.

Answer 6

You can use DATE_FORMAT() function in order to get the first day of any date field.

SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01') as FIRST_DAY_CURRENT_MONTH 
FROM dual;

Change Curdate() with any other Date field like:

SELECT DATE_FORMAT(purchase_date,'%Y-%m-01') AS FIRST_DAY_SALES_MONTH 
FROM Company.Sales;

Then, using your own question:

SELECT *
FROM
  hrm_attendanceregister
WHERE
hrm_attendanceregister.Date) >=
 DATE_FORMAT(CURDATE(),'%Y-%m-01')

You can change CURDATE() with any other given date.

Answer 7

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

Answer 8

I'm surprised no one has proposed something akin to this (I do not know how performant it is):

CONCAT_WS('-', YEAR(CURDATE()), MONTH(CURDATE()), '1')

Additional date operations could be performed to remove formatting, if necessary

Answer 9

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.

Answer 10

use date_format method and check just month & year

select * from table_name where date_format(date_column, "%Y-%m")="2010-06"

Answer 11

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

date=your column name

Answer 12

date_add(subdate(curdate(), interval day(?) day), interval 1 day)

change the ? for the corresponding date

Answer 13

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")

Answer 14

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'));

Answer 15

I might be late for this answer but this works for me

SELECT DATE_FORMAT(datetime_column, '%Y-%m-01') AS first_date, LAST_DAY(datetime_column) AS last_date FROM your_table;

Answer 16

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.