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

Question

Hi,

I want to get first day of every corresponding month of current year. For example, 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

use date_format method and check just month & year

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

Answer 2

Is this what you are looking for:

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

Answer 3

You could explode the given date into its year month and date elements and then use the date function along with mktime to get the first day of the month:

$date = "2010-06-15";
list($year, $month, $day) = explode("-",$date);
$firstOfMonth = date(('Y-m-01'), mktime(0,0,0,$month,$day,$year));

Answer 4

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.