MySQL date help? Getting a week span?

brianb

Hello,

I have a date like 2003-03-07 which is a Friday...

Can someone let me know how to get a week's span that this date lies in between, which starts on Monday and end on Sunday through MySQL?

Example. The date above would return:

March 3rd - March 9th

Any help is appreciated, ESPECIALLY coding examples!!!

Thanks!

Gargouil

with the date function you can extract the day of the week (1 - 7) then calculate how many day passed since 1 then how many day remain since 7 then compute

use a mktime() or strtotime() function to finalize

PyroX

Another way, I think the better way, would be to use:

$today=date('m/d/Y');
$week=date('m/d/Y',(time()+604800));

604800 is how many seconds are in a week.

time() returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

I tested this for you:

<?
echo date('m/d/Y');
echo '<BR>';
$week=date('m/d/Y',(time()+604800));
echo $week;
?>

Which output:

03/14/2003
03/21/2003

Should work best for you.

brianb

Thansk for the replies guys....

Gargouil can you give me a bit of example code? I think I'm a bit lost, but I think that's the right track...

Thanks in advance.

ejrhodes

Originally posted by PyroX
Another way, I think the better way, would be to use:

$today=date('m/d/Y');
$week=date('m/d/Y',(time()+604800));

604800 is how many seconds are in a week.

time() returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

I tested this for you:

<?
echo date('m/d/Y');
echo '<BR>';
$week=date('m/d/Y',(time()+604800));
echo $week;
?>

Which output:

03/14/2003
03/21/2003

Should work best for you.

Wont this return the span of 1 week past the current date, not the span of the week that that day is in. For instance, if the day is a tuesday, i think he wants the span of monday-sunday, not tuesday to tuesday. How would you do that?

brianb

Yeah....the code posted isn't what I'm really looking for....If I give you Tuesday of this week, I want to return the MOnday-Sunday span that Tuesday is in. Make sense?

Let me know if anyone has any ideas. The first reply seems to be on the right track....I just have no idea how to pull it off.

Thanks people!

adamgeorge

Hey, how it's going.

well, if Monday is day 0 and sunday day 6, work out what date is in relation to that...

so in MySQL:

select dtMyDate - INTERVAL weekday(dtMyDate) DAY, dtMyDate + INTERVAL (6 - weekday(dtMyDate)) DAY from tbMyTable;

that will give you the date of the Monday and Sunday in relation to the week for the date dtMyDate.

Use the MySQL function format_date() to then format those dates to you required format.

I got all those MySQL functions and syntax from the MySQL user documentation...

cya man,
-Adam 🙂