Good day - I've worked with MS Access and VBA, but I'm brand new at PHP and MySQL. To aid my self-education in PHP/MySQL, I decided to create a site that uses date comparisons to determine the content of an individual page. While working with my test data last night, I noticed some behavior that I thought was odd and I wonder if I should try a different column tyoe or something. Or there may not be a real problem. Here's what I have, and a description of the problem - I have a number of fields in the table (named "event-data"), and one of them is a field containing a date. I specified column type "Date" for that column. The object of the page/s is to display data from those rows where the date in the row is equal to or greater than the current date as determined by a comparison to the server's date. The date format is YYYY-MM-DD. I have about 60 sample rows in the database and, of those, perhaps 50 should display - that is, the dates are on or after today. I have not written the queries for the Web pages yet. I was just experimenting last night writing and running queries using PHPMyAdmin. I found that this query - SELECT * FROM event_data WHERE date>= 1/15/2008 would return all of the rows with valid dates, PLUS some rows with dates as early as December 17th, 2007. But there are other rows in the DB with dates prior to the 17th that were not returned. Either way, that's obviously not the correct result set. Changing the query to - SELECT * FROM event_data WHERE date>= "1/15/2008" (added double quotes around the date to compare against) produces correct results. And I can add other conditions to the latter query to reduce the size of the result set and it still gets the date selection correct. It strikes me as odd that the results should be correct when the date is compared to the double-quoted date, and mostly correct without the double quotes. Should I rewrite the query to use a different format for the date? Use a different column type in the DB? Or should I use the double quotes around the date in the query? I've looked through the reference books I have and tried to look this up on the Web, but haven't found anything that seems to address the question as I understand it. Thank you in advance for any help. Steve E.
I don't see why there would be anything wrong with using quotes. I'd use the YYYY-MM-DD format in your queries though just so you get in the habit.
you should always use the mysql native date format, otherwise you can make your life pretty difficult. try ... where mydate >= '2007-09-08 00:00:00' AND mydate <= '2007-09-08 23:59:59' Code (markup): to find a certain date.