CODEWITHSUNDEEP
mysqldate
Ben
Ben

Reputation: 62434

Comparing Dates in yyyy-mm-dd format

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?

Upvotes: 1

Views: 2607

Answers (5)

Kaleb Brasee
Kaleb Brasee

Reputation: 51955

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.

Upvotes: 0

John Parker
John Parker

Reputation: 54445

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()

Upvotes: 0

davek
davek

Reputation: 22925

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.

Upvotes: 0

Yada
Yada

Reputation: 31225

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().

Upvotes: 1

Pekka
Pekka

Reputation: 449605

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())

Upvotes: 1

Related Questions