[SOLVED] Working with dates

[dannyo101]

Hi - I have some code that calculates the next monday date based on the current date.  I'm trying to set the monday date to disregard the time in the current date and default to 11:59 PM.  I almost have it but getting stuck on the time part.

 

<?

include 'db.php';

$test_date = '2007-11-26 23:59:59';

$sql = mysql_query("select  now(),
					case when DAYOFWEEK('$test_date')=2 then '$test_date'
					when DAYOFWEEK('$test_date')=1 then DATE_ADD('$test_date', INTERVAL 1 DAY)
					else DATE_ADD('$test_date', INTERVAL 9-DAYOFWEEK('$test_date') DAY) end
				from	sv_schedule") or die (mysql_error());
$row = mysql_fetch_row($sql);

echo "Current date and time: $row[0]<br />";
echo "Test date and time: $test_date<br />";
echo "Monday's date: $row[1]<br />";

?>

 

I want to be able to use any date and time in $test_date and have the monday date always be at 11:59 PM.

 

Any help would be appreciated.

[fenway]

Easient way is to extract the date part w/DATE() and concat the time that you want.

[dannyo101]

I actually have another way that work as well.  I'm going to use curdate() instead of now() which will give me the date only (minus the time).  I'll rewrite my case statement to return tuesday instead of monday then substract 1 second from it.  Something like this:

 

curdate()-INTERVAL 1 second

 

I tested my code for every day of the week and its working...

 

 

<?

include 'db.php';

$test_date = '2007-08-25';

$sql = mysql_query("select  now(),
	         case when DAYOFWEEK('$test_date')=2 then (DATE_ADD('$test_date', INTERVAL 1 DAY))-INTERVAL 1 second
	         when DAYOFWEEK('$test_date')=1 then (DATE_ADD('$test_date', INTERVAL 2 DAY))-INTERVAL 1 second
                                  else (DATE_ADD('$test_date', INTERVAL 10-DAYOFWEEK('$test_date') DAY))-INTERVAL 1 second end
	         from sv_schedule") or die (mysql_error());
$row = mysql_fetch_row($sql);

echo "Current date and time: $row[0]<br />";
echo "Test date: $test_date<br />";
echo "Monday's date: $row[1]<br />";

?>

 

Thanks anyways!