19

How can I get the rows in a table where today's date is between (inclusive) two DATE columns of that row? For example, take these two columns of a table:

enter image description here

How could I get the first and second rows on the 10th of April, or the 3rd row on the 25th (inclusive, like I said)?

Any help would be greatly appreciated. Thanks in advance!

JJJollyjim
  • 5,837
  • 19
  • 56
  • 78
  • looks similar problem with https://stackoverflow.com/questions/3422576/how-to-check-now-falls-between-two-specific-dates – Sofyan Thayf Dec 02 '18 at 05:49
  • Possible duplicate of [how to check now() falls between two specific dates?](https://stackoverflow.com/questions/3422576/how-to-check-now-falls-between-two-specific-dates) – Sofyan Thayf Dec 02 '18 at 05:51

4 Answers4

31

You can add a condition as follows

DATE(NOW()) between date1 and date2
Chetter Hummin
  • 6,687
  • 8
  • 32
  • 44
27

You will find a lot of people using between operator, but I prefer using a simple AND operator.

I do that because although the between operator IS inclusive, simple dates (2012-04-10) can be counted as being midnight, and will thus not be inclusive.

So this should work just fine and will always include the boundaries of the date range:

SELECT * FROM table WHERE from_date <= '2012-04-10' AND to_date >= '2012-04-10'
Rob
  • 26,989
  • 16
  • 82
  • 98
Bill
  • 506
  • 1
  • 7
  • 15
7

Just use the SQL now() function to compare the date columns like so:

SELECT * from table where now() >= from_date and now() <= to_date
Eric Sites
  • 1,494
  • 10
  • 16
  • 2
    Use CURDATE() instead, Note that if you use NOW() it includes time information and can lead to exclude some dates, ie next function returns false (0): select "2010-01-01 14:00:00" <= "2010-01-01" – CesarC Apr 18 '12 at 04:57
1

If you have date (not datetime) columns, use CURTIME() or DATE(NOW()), never NOW() as CesarC correct wrote and you can use BETWEEN.

SELECT * FROM table WHERE CURTIME() BETWEEN from_date AND to_date

podolinek
  • 166
  • 2
  • 12