is there sql notation that can tell check between months?

Question

I'm storing recurring events in an events table that take place during certain months of the year, any year and every year. For instance;

CREATE TABLE events ( 
  event_id tinyint(3) unsigned NOT NULL auto_increment, 
  name varchar(255) NOT NULL, 
  month_from tinyint(3) unsigned NOT NULL, 
  month_to tinyint(3) unsigned NOT NULL, 
  PRIMARY KEY (event_id) ) 
ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3; 

INSERT INTO events 
  (event_id, name, month_from, month_to) 
VALUES 
  (1, 'Event ABC', 5, 11), (2, 'Event XYZ', 12, 4);

Event ABC - takes place every year during May - Nov and Event XYZ - takes place every year during Dec - Apr

In my events table I'm storing the month_from and month_to as numeric values.

What I want to be able to do is say take the current month (Oct) and pass it into an sql query for it to return to me the correct event "Event ABC". But also I want to pass to it a future month of say Feb it should return to me "Event XYZ"

I hope that makes sense?

Answer 1

select * from tbl
where month_from <= <YOUR_MONTH> and month_to >= <YOUR_MONTH>

providing that <YOUR_MONTH> is a value that semantically equivalent to the month_from and month_to fields.

UPDATE: Just noticed the second example from Dec to Apr. Maybe you can create two ranges for the event Dec to Dec and Jan to Apr.

Answer 2

Try:

 FROM TABLE t
WHERE @month BETWEEN t.month_from AND t.month_to

If you want to pass a date & get the month out of the date for the comparison:

 FROM TABLE t
WHERE MONTH(@your_date) BETWEEN t.month_from AND t.month_to

Reference: MONTH

Answer 3

This should do it:

SELECT * FROM events WHERE month_from <= $your_month AND month_to >= $your_month

You are not limited to months as integers. You can use the DATE datatype with MySQL (or equivalent for your database) and use the same query as above. Your database will "understand" the <,>,= operators for your DATE fields.

Answer 4

select * from tbl where (month_from <= $YOUR_MONTH and month_to >= $YOUR_MONTH) or (month_from > month_to and (month_to >= $YOUR_MONTH or month_from <= $YOUR_MONTH)

this will also work for DEC-APR ranges