0

I read how I can query between two dates here, but I'm trying to do the opposite. I have a database of events, with start days and end days. I'm trying to check to see if now() is between any start days AND end days.

Of note, my events are cyclical so I don't care about the years. To use the data format in my database I just use 2000 for everything. I have events such as the Christmas season (2000-12-01 to 2000-12-25), Independence Day (2000-06-25 to 2000-07-04), and so forth.

At first I was thinking about doing something like:

SELECT * FROM `table` WHERE MONTH(NOW()) >= MONTH(`start`) AND DATE(NOW()) >= DATE(`start`) AND MONTH(NOW()) <= MONTH(`end`) AND DATE(NOW()) <= DATE(`end`)

But then I realized a major flaw in periods of time that span multiple months. If today is 27JUN then I'd like the Independence Day row to return, but while 27 >= 25, 27 <= 4 is not the case.

I was thinking of seeing if I can convert NOW(), start, and end to a timestamp to allow me to compare them, but discounted the idea because I might have something with a start date of 2000-12-26 and an end date of 2000-01-01 (remembering that I'm ignoring the year).

Is there a better way to store month/date pairs instead of dates? Or what can I do to fix my database query?

GFL
  • 1,278
  • 2
  • 15
  • 24

2 Answers2

1

The current date has to be adjusted back to year 2000 before comparing to start and end dates. If the end date is before start date, the year of the end date is actually the year after the start date. Try this:

SELECT * from `table`
WHERE
(`end` >= `start` AND 
 DATE_ADD(CURDATE(), INTERVAL 2000-YEAR(CURDATE()) YEAR) 
 BETWEEN `start` AND `end`)
OR
(`end` < `start` AND 
 DATE_ADD(CURDATE(), INTERVAL 2000-YEAR(CURDATE()) YEAR) 
 BETWEEN `start` and DATE_ADD(`end`, INTERVAL 1 YEAR))

OR: 

SELECT * from `table`
WHERE
CASE 
WHEN `end` >= `start` THEN 
DATE_ADD(CURDATE(), INTERVAL 2000-YEAR(CURDATE()) YEAR) BETWEEN `start` AND `end`
ELSE
DATE_ADD(CURDATE(), INTERVAL 2000-YEAR(CURDATE()) YEAR) BETWEEN `start` and DATE_ADD(`end`, INTERVAL 1 YEAR)
END

Use CURDATE() instead of NOW() cos NOW() has the time component that is not needed.

kc2018
  • 1,440
  • 1
  • 8
  • 8
0

SELECT * FROM table WHERE CURDATE() between dateStart and dateEnd

where dateStart is your past date and dateEnd is your future date

Gsk
  • 2,929
  • 5
  • 22
  • 29
Sagar Pandit
  • 1
  • Unfortunately that doesn't return any results since it takes into account the year, with 2018 being well past 2000. – GFL Apr 11 '18 at 06:51