Jump to content


Photo

mySQL Datetime problems


  • Please log in to reply
3 replies to this topic

#1 dannyo101

dannyo101
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 24 August 2006 - 12:10 AM

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.


$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


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!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 August 2006 - 09:54 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 dannyo101

dannyo101
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 03 September 2006 - 01:12 AM

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

$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");

Any other ideas?  I appreciate the help.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 September 2006 - 03:39 PM

You should just be able to CONCAT() the time on to the result of your case statement.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users