Trying to filter a mysql table by date using a single query.

Question

Hi there, i'm trying to request any records from a table that appear on or after todays date, using a single query. All the dates for each record are stored in seperate columns eg. - one for 'month', one for 'year', and one for 'day'.

obviously i've been getting all records that occur after the year in today's date

$sql = "SELECT * FROM table WHERE year>=".$year_today." ORDER BY year, month, day";

Then i've been trying to filter that down a bit, by using:

$sql = "SELECT * FROM table WHERE year>=".$year_today." && month>=".$month_today." && day>=".$day_today." ORDER BY year, month, day";

And in order to test it, i created a record in the database with YESTERDAYS date, yet, this record still appears in the list of returned records. What am i doing wrong? :(

Answer 1

I don't know if you can use "&&" instead of "AND" in your context - maybe try changing your SQL to use

" AND month>=".$month_today." AND day>=".$day_today."

EDIT :

as an extension to Nick's answer, to make the sql behave correctly you could code :

$sql = "SELECT * FROM table WHERE (  
(year>".$year_today.")
OR (year=".$year_today." && month>".$month_today." )
OR (year=".$year_today." && month=".$month_today."&& day>=".$day_today.")
)
 ORDER BY year, month, day";

...but this starts to get a whole lot messier than converting to dates and using date comparison

EDIT2:

but then again, if these columns are indexed, the indexes might be used. It's still a lot more effort to code SQL this way if performance using conversion to dates is totally acceptable.

Answer 2

This can be achieved using time functions in a nice way, while it's better when using a DATE column.

SELECT * FROM TABLE WHERE TIMESTAMP(CONCAT(year,"-",month,"-",day)) >= CURDATE()

ok, that's evil as it doesn't use an index ... proper thing would be a DATE column, but doing all this by hand is annoying asyou also have to consider the case where year is bigger but months is smaller and stuff ....

Answer 3

To compare dates with dates:

SELECT * FROM table WHERE 
  STR_TO_DATE(
    CONCAT(year
    , '-'
    , right(concat('00', month), 2)
    , '-'
    , right(concat('00', day), 2))
  , '%Y-%m-%d')

>= STR_TO_DATE('2009-12-11', '%Y-%m-%d')

Answer 4

to check if a date is after another date this query will fail:

$sql = "SELECT * FROM table WHERE year>=".$year_today." && month>=".$month_today." && day>=".$day_today." ORDER BY year, month, day";

day 12 march of februari is smaller then day 15 in februari, but is still after it, because it's in a different month....