Posted by tg-php on 05/11/05 23:11

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.

[Back to original message]