how select different month of a year

[scorpioy]

I have a table that has a column containing date and time information with format of 0000-00-00 00:00:00, I want to know how to write query to get rows that are in 2006 Jan, Feb, Mar, ..., return information on a monthly period.

I use PHP to allow users to input the 2006-01, 2006-02, in my code, I check if value is 2006-01, then I set two variables to '2006-01-01' and '2006-01-31'; if the value is 2006-02, then set the values to '2006-02-01' and '2006-02-28'. But I hope there is better way instead of writing if condition one by one. Does someone have a better way to do it?

Thanks.

[gardnc]

$next_month = date("Y-m",strtotime("+1 month", strtotime($entered_date . "-01")));

example:

<?php
$entered_date = "2006-04";
$next_month = date("Y-m",strtotime("+1 month", strtotime($entered_date . "-01")));
print "$entered_date - $next_month";
?>

Larry

[scorpioy]

thanks for the reply, gardnc, but that doesn't produce the beginning and ending date of each month I want, so that it can be used in the range of each month.
Any other ideas?

[bogu]

SELECT DATE_FORMAT(`date`,'%Y-%m') FROM table

this will select field "date" from db under a special format (ex. 2006-01, 2006-02, 2006-03)

if u want to create date range values and u want to know the number of days a month have, then there is a php function that do that for u:
Ex.

$month = date('n');
$year = date('Y');
$num = cal_days_in_month(CAL_GREGORIAN, $month, $year);

For what example u made it should go like this

	for ( $month=1; $month<13; $month++) {
		/**
		 * this :str_pad($month,2,'0',STR_PAD_LEFT)
		 * will put '0' in from on a string if the string is less then 2 caracters long
		 * ex. is month = 3 then str_pad($month,2,'0',STR_PAD_LEFT) returns 03
		 **/
		$cal[$month]['first_day'] = "2006-".str_pad($month,2,'0',STR_PAD_LEFT)."-01";
		$cal[$month]['last_day'] = "2006-".str_pad($month,2,'0',STR_PAD_LEFT)."-".cal_days_in_month(CAL_GREGORIAN, $month, 2006);
	}
	echo "<pre>",print_r($cal,1),"</pre>";

[gardnc]

scorpioy,

The code I gave plus adding the "-d" for the day if the date format gives the first day of the next month. The last day of the month is always the next month less one day - so

$entered_date = "2006-04";
$next_month = date("Y-m-d",strtotime("+1 month", strtotime($entered_date . "-01")));
print "$entered_date - $next_month<br>";

$next_month_end_date = date("Y-m-d",strtotime("-1 day", strtotime(date("Y-m-d",strtotime("+1 month", strtotime($next_month))))));

print $next_month_end_date;

[Installer]

Or:

$next_month_end_date = date('Y-m-t', strtotime($next_month));