Jump to content

Archived

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

dannyo101

mySQL Datetime problems

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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
You should just be able to CONCAT() the time on to the result of your case statement.

Share this post


Link to post
Share on other sites

×

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.