mySQL Datetime problems

[dannyo101]

I've created a case statement that will look at a date and return the next Monday.  If the date is a Monday, it will return that date.  What I'm trying to do is return the Monday date and have the time always set to 23:59:59, and this is where I'm running into problems.

[code]

$test_date = '2006-08-08 13:09:59';

select case when date_format('$test_date', '%w') > 1 then DATE_ADD('$test_date', INTERVAL 8-date_format('$test_date', '%w') DAY)
when date_format('$test_date', '%w') = 0 then DATE_ADD('$test_date', INTERVAL 1 DAY) else '$test_date' end

[/code]

With the code above, a datetime value of 2006-08-14 13:09:59 will be returned.  I would like a datetime value of 2006-08-14 23:59:59 returned.

I'm thinking I can setup a variable with the value of 23:59:59 and only use the date part in my CASE statement. Then I could put the 2 values together...not sure if this is the best way to go about it.  Just looking for a little help.  Thanks!

[fenway]

Well, first, you can use DAYOFWEEK() for a more clearer algorithm; as far as the time, simply extract with DATE() and then CONCAT() on the time you want.

[dannyo101]

I've been trying to get the Date() function working in my script but haven't been able to. I finally realized that the version of mySQL doesn't suport it.  I'm running on MySQL 4.0.18

I did clean up my algorithm with DAYOFWEEK() but I'm still having problems getting just the date and concatenating the time of 23:59:59

[code]
$test_date = '2006-08-08 13:09:59';

$result = mysql_query("SELECT case when DAYOFWEEK('$test_date') > 1 then DATE_ADD('$test_date', INTERVAL 8-DAYOFWEEK('$test_date') DAY)
when DAYOFWEEK('$test_date') = 0 then DATE_ADD('$test_date', INTERVAL 1 DAY) else '$test_date' end");
[/code]

Any other ideas?  I appreciate the help.

[fenway]

You should just be able to CONCAT() the time on to the result of your case statement.