RE: [PHP] Re: SQL Date guru in the house?

Cool thanks for that. After reading through the strings manual pages on mysql site I came up with this way of doing it. Not as simple as yours that's for sure, but it works fine. SELECT blah WHERE CONCAT(yyyy, LPAD(mm,2,'0'), LPAD(dd,2,'0')) BETWEEN ". $yyyy . $mm . $dd ." AND ". $e_yyyy . $e_mm . $e_dd ." That works as well. Thanks a pile for the pointer tho. Regards M -----Original Message----- From: tg-php@gryffyndevelopment.com [mailto:tg-php@gryffyndevelopment.com] Sent: Thursday, 12 May 2005 5:41 AM To: php-general@lists.php.net Cc: Matthew Western, IT Support, Lonsdale Subject: Re: [PHP] Re: SQL Date guru in the house? Sorry, don't have time to look up the specifics.. and I've worked with a number of different flavors of SQL, so not sure the syntax or capabilities of the system you're using, but maybe try something like this: SELECT * FROM blah WHERE DATE(mm, dd, yyy) BETWEEN $date1 AND $date2 Basically convert the output of the columns to a date format and do the comparison that way. Some DATE commands are in the format DATE(MM, DD, YYYY), some have commands to convert a string to a date: STR_TO_DATE(MM + "/" + DD + "/" + YYYY), etc. That way you get a serial datestamp to work with which should make finding things within that range a ton easier. Good luck! -TG = = = Original message = = = Hello, on 05/11/2005 03:17 AM mwestern@sola.com.au said the following: > I have a small problem. > > I have a project in which someone has got three integer fields for > holding the date. DD, MM, YYYY in an sql database. I now have to > have a page that inputs two dates and select records between those two > dates. > > If I had a date field in the table it would be fairly simple, but I'm > hoping to do this search/comparison without having to rewrite the > pages/database that has already been designed. > > > Start Date:~11/05/2005 > End Date:~11/04/2005 > SELECT * FROM blah WHERE mm BETWEEN 04 AND 05 AND dd BETWEEN 11 AND 11 > AND yyyy BETWEEN 2005 AND 2005 > > Doesn't work for obvious reasons. Is there any way that I can do > this date comparison I the SQL statement without having a decent date > field? > My apologies as this is australian date format and this list is in the > US I think? The format is only relevant for outputing dates. For querying you can use the date values directly to delimit the range that you want SELECT * FROM blah WHERE mm BETWEEN '11/04/2005' AND '11/05/2005' -- Regards, Manuel Lemos PHP Classes - Free ready to use OOP components written in PHP http://www.phpclasses.org/ PHP Reviews - Reviews of PHP books and other products http://www.phpclasses.org/reviews/ Metastorage - Data object relational mapping layer generator http://www.meta-language.net/metastorage.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php