Comparing Dates in yyyy-mm-dd format

Question

There's a query I'm working on that has

... WHERE start_date >= DATE(NOW()) AND end_date >= DATE(NOW())

start_date/end_date are in the date format: yyyy-mm-dd

What kinds of results should be expected from this kind of query?

Answer 1

What kinds of results should be expected from this kind of query?

If start_date and end_date are proper mySQL DATE fields, it should work just fine and return all entries where start_date AND end_date are today's date, or a date in the future.

If they are varchar fields, you may want to consider converting the columns into DATE fields - I assume the values are going to remain but make a backup of course - or casting them as DATE fields (slow!):

WHERE CONVERT(start_date, DATE) => DATE(NOW()) AND CONVERT(end_date, DATE) => DATE(NOW())

Answer 2

Both start_date and end_date is greater than or equal to today's date.

Instead of using DATE(now()) you can use CURRENT_DATE().

Answer 3

you will be comparing dates at the date (i.e. time not considered) level of granularity. i.e. all rows where the day is on or later than today (NOW()), assuming that start_date and end_date are date columns.

Answer 4

This should work fine, as you can use NOW() within a date comparision.

However, you don't need to wrap NOW() within DATE - it'll work as-is as long as you're using one of the time related (DATE, DATETIME, TIMESTAMP, etc.) field types.

As such, you should just be able to use...

WHERE start_date >= NOW() AND end_date >= NOW()

Answer 5

That would work, provided that you surrounded start_date and end_date with single quotes. It would give you all the records where the start date and end date are in the future.