Mysql - check if date is in some range

Question

Assume that we have a table like this:

(ID, StartDate, EndDate, SubjectID)
(1, '2018-01-01', '2018-02-01', '1')
(2, '2018-03-01', '2018-04-01', '1')
(n, '2018-06-01', '2018-06-15', '1')

For the specified date (for example '2018-04-04'), i want to check if this date is in any of existing ranges for that subject. How to do this in Mysql? Problem is that number of ranges is variable per subject (we can have 1 to n rows (ranges) per subject).

Possible duplicate of [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) — Ibu, Oct 26 '18 at 23:20

score 2 · Answer 1 · answered Oct 27 '18 at 01:42

You can very easily get the required output using the "Greater Than Equal To" and the "Less Than Equal To" filter in mysql. Simply place the query as

Select SubjectID From your_table
Where StartDate <= '2018-04-04' 
AND EndDate >= '2018-04-04';

It should do the trick. Cheers.

score 1 · Answer 2 · edited Oct 27 '18 at 01:37

1

You can simply do this:

SELECT DISTINCT SubjectID FROM table_name WHERE StartDate < '2018-04-04' 
  AND EndDate > '2018-04-04';

edited Oct 27 '18 at 01:37

Nick

138,499
22
57
95

answered Oct 26 '18 at 23:29

Pritam Banerjee

17,953
10
93
108

Mysql - check if date is in some range

2 Answers2