Week range from Date

cmptrwhz

Hi All --

Hope you can help me. I have a script that pulls a date from a mysql table. I need to be able to find the Week of the year of the date, then pull the Monday date and Sunday date (7 days apart).. or just based on the date in the DB, get those dates?

I have searched the forums in great length, and didn't really see anything along this line of code.

Any suggestions would be greatly appreciated.

Thanks In Advance.

Mike

AstroTeg

Check out mysql's web site: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

On there is a SQL function called WEEK(). You could use that to compare the week value of the date you found and all the dates in your database and just grab those that match. Example:

SELECT ... FROM ... WHERE WEEK('01/01/2004') = WEEK([your date field here])

That might work. Don't know if you have any other requirements that might defeat this...

cmptrwhz

k.. maybe I need to be clearer..

Let's say that I have a date that is a Wednesday. I need to know what the Monday and Friday dates are.

This is for a time card app.. so you can see where I'm going with this.

Thanks for your help.

Mike

AstroTeg

I had to do this in Zope/Python the other day. PHP gives you a ton more options. You have [man]mktime[/man] available which is rather flexible (meaning you can usually give it a very bad date and it'll try to make sense of it). One approach would be to use this function and subtract or add the number of days you need to use to get to Monday or Friday based on your current date. This will mean you'll need to figure out what day of the week it is (use [man]date[/man] to do this). You might look into [man]strtotime[/man] for it may help you out depending on what ideas you have.

Jocke

Take a look at the date() function. Here are a few examples.

$date = 20050301;

//nr of the week (in this case 9)
echo date(W,strtotime($date));

//numeric representation of the day of the week, this will output 2 (tuesday)
//Note: 0 is sunday and 6 is saturday
$day_nr = date(w,strtotime($date));
echo $day_nr;

//calculate nr of days left to next sunday
if($day_nr){
	$sunday_nr = 7 - $day_nr;	//If it's not a sunday, calculate how many days to next sunday
}else{
	$sunday_nr = 0;				//If it is a sunday, it is 0 days to sunday...
}

//Echos the date on sunday (20050306), the same week as 20050301
echo "sunday: ".date("Ymd",strtotime($date + $sunday_nr));