Jump to content

mySQL Datetime problems


dannyo101

Recommended Posts

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!
Link to comment
https://forums.phpfreaks.com/topic/18489-mysql-datetime-problems/
Share on other sites

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.