Mysql date query to find if the current is within two dates?

Question

In my table I have two fields: start_date and end_date. I'm just trying to retrieve rows where the current date is between the start and end dates. For some reason, I am not getting this at all. I've tried using between, but I keep getting empty sets even though I know that there are records that exist... I don't think I'm thinking about this clearly... Anyone know how to do this?

Answer 1

SELECT  *
FROM    mytable
WHERE   SYSDATE() BETWEEN start_date AND end_date

Make sure your start_date and end_date are DATE or DATETIME, not VARCHAR.

Answer 2

Here's another way to do it without using BETWEEN:

WHERE @date >= start_date AND @date <= end_date

It should be equivalent though.

Also note that often what you really want is a half-open interval:

WHERE @date >= start_date AND @date < end_date

If you are paging, using a half-open interval avoids repeating values at the end of one page at the start of the next. You can't express this with the BETWEEN syntax.