From: "Ashley M. Kirchner" <[EMAIL PROTECTED]> > I need to query for record within a certain date stamp. The > datetime field contains the createdon information that i need and is in > the following format: 2004-01-11 21:40:50 > > What I'd like to do is search for records that are between > 2004-01-09 and 2004-01-04. The time is to be ignored, just the date is > what I need to search by. Is there a way to do this within an sql > query, or do I have to do some PHP processing of the datetime field to > get what I need?
You'll want to do it in the query, although PHP could help a little. Even though you want the time to be ignored, the easiest way to do this is to actually search from 2004-01-09 00:00:00 through 2004-01-04 23:59:59. Since the "00:00:00" and "23:59:59" are "constants" you can just have PHP add them to the date string or hard code them into your query. Given that format, the query would be as simple as: SELECT * FROM table WHERE datetimefield BETWEEN $start AND $end Where $start is "2004-01-09 00:00:00" and $end is "2004-01-04 23:59:59" If you don't want to do that processing, then you'll need to use another method. SELECT * FROM table WHERE TO_DAYS(datetimefield) BETWEEN TO_DAYS($start) AND TO_DAYS($end) Where $start is "2004-01-09" and $end is "2004-01-04". There are other methods, too, but the end results is you need to take your "datetimefield" and get it into the same format as $start and $end. If you're using MySQL 4.1, there is a DATE() function (I think) that'll allow you to do this: SELECT * FROM table WHERE DATE(datetimefield) BETWEEN $start AND $end Where $start is "2004-01-09" and $end is "2004-01-04". Hope that helps. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
