Re: Re: SQL Date guru in the house?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx 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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux