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

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 firs开发者_如何学Ct 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

Is this what you are looking for:

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

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

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

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;

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.

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.

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

use date_format method and check just month & year

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

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

date=your column name

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

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.

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

change the ? for the corresponding date

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

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

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.