|
|
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.
Navigation:
[Reply to this message]
|