Date Issue using to_days

rpupkin77

I am trying to run a query that picks out ads that have a run date between two user inout dates ($start and $end). In preceeding code I take values from two sets of lists to form a date format as such: yyy-mm-dd.
The query runs without any probelem yet no results are returned, any ideas?

SELECT ads.ad_id, ads.type, ads.run_date, papers.paper_name, clients.name, ads.colors, ads.size, ads.description FROM ads, papers, clients WHERE to_days(ads.run_date) > to_days(2003-01-01) AND to_days(ads.run_date) < to_days(2003-06-01) AND ads.client = clients.cid AND papers.paper_id = ads.paper_id

jkohns

why not just use BETWEEN '$start' AND '$end' ?

rpupkin77

the functionality would be the same, the main issue I am having is with the date functions.

trooper

Listen to the man jkohns

Use the BETWEEN function

😃

rpupkin77

I did - same issue.

jkohns

I'm not sure what the issue here is then- but I do notice you use the TO_DAYS() function an awful lot. TO_DAYS() is limited in that it does one thing- calculate the number of days from year 0. It's not a the best way to compare two dates. MySQL has a lot of very useful Date & Time functions that make just about any date calulation pretty simple. When possible, I try to perform all my date calculations in my queries (just a preference for me).

When fetching dates from a table, and using them in calculations in PHP, it's usually best to use the UNIX_TIMESTAMP() function of MySQL- this formats the date output in the same Unix Timestamp form that PHP's date functions use- makes things work out pretty nicely that way.

http://www.mysql.com/doc/en/Date_and_time_functions.html