Clever Geek Handbook

Is there sql notation that can check validation between months?

I store recurring events in an event table that occurs in specific months of the year, every year and every year. For example,

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

ABC Event - held every year during May - November and XYZ Event - held every year during December - April

In my table I keep events month_from

and month_to

as numeric values.

What I want to do is take the current month (October) and feed it to a SQL query so that it will return the correct "ABC Event" to me. But also I want to give him next month, say Feb, he should return to me "Event XYZ"

Hope this makes sense?

+2


source to share


4 answers


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 bands

+1


source


Try:

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

If you want to pass in a date and get the month from the date for comparison:



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

Link: MONTH

+1


source 


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

provided that <YOUR_MONTH>

is a value that is semantically equivalent to the month_from and month_to fields.

UPDATE: just noticed the second example from December to April. Perhaps you can create two ranges for Dec to Dec and Jan to Apr events.

0


source


This should do it:

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

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

0


source






More articles:


All Articles