bschultz Posted May 30, 2009 Share Posted May 30, 2009 This query isn't returning what I thought it would... $sql = "SELECT * FROM content WHERE NOW() BETWEEN startdate AND enddate"; Both startdate and enddate are datetime formats in the db. What is the correct syntax for this query? Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/ Share on other sites More sharing options...
bschultz Posted May 30, 2009 Author Share Posted May 30, 2009 server version is 5.0.32 by the way... No errors, just showing events that had an enddate of a few hours ago. Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845431 Share on other sites More sharing options...
roopurt18 Posted May 30, 2009 Share Posted May 30, 2009 Is the MySQL server in a timezone that is earlier in the day than the one you are physically in? Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845432 Share on other sites More sharing options...
bschultz Posted May 30, 2009 Author Share Posted May 30, 2009 nope...it's sitting right next to me Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845440 Share on other sites More sharing options...
roopurt18 Posted May 30, 2009 Share Posted May 30, 2009 Well you said there are no errors and it's returning results, just that some of them have expired enddates. The only thing I can think of is NOW() is not what you're expecting it to be, which means the clock on that machine may be wrong. Try this: $sql = "SELECT *, now() as thenow FROM content WHERE NOW() BETWEEN startdate AND enddate"; Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845452 Share on other sites More sharing options...
bschultz Posted May 30, 2009 Author Share Posted May 30, 2009 Thanks for the help! The server time is correct...just checked it. Your code still displayed expired results. The one record that's displaying wrong (the only one that's expired) has this: 2009-05-29 17:00:00 as it's enddate value. As you can see on this page (http://www.kkbj.com/datetime.php) the time is right...and the enddate field in the db is before the current time. Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845458 Share on other sites More sharing options...
roopurt18 Posted May 30, 2009 Share Posted May 30, 2009 What's the entire result that is incorrect? Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845537 Share on other sites More sharing options...
bschultz Posted May 30, 2009 Author Share Posted May 30, 2009 All is working now. I had changed the field type from date to datetime, and thought it was working...until it kept displaying the outdated entries. I deleted the table and started over (with datetime as the field types), and now it's working just fine. Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/160219-solved-select-where-now-between-start-and-end/#findComment-845828 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.