MySQL query to get rows on specific date

Question

ID |    STARTS     |  ENDS
1   |  2016-04-13  |  2016-04-14
2   |  2016-04-13  |  2016-04-15
3   |  2016-04-13  |  2016-04-15
4   |  2016-04-13  |  2016-04-16
5   |  2016-04-13  |  2016-04-19

I need to get the events on 2016-04-16 . So the result should be

4 | 2016-04-13 | 2016-04-16
5 | 2016-04-13 | 2016-04-19

What will be the mysql query?

Possible duplicate of Retrieve data Mysql / Php where date

The_Black_Smurf
– The_Black_Smurf

2016-04-13 18:21:10 +00:00
Commented Apr 13, 2016 at 18:21 — The_Black_Smurf
– The_Black_Smurf, Commented Apr 13, 2016 at 18:21

Fabricator · Accepted Answer · 2016-04-13 18:11:54Z

1

Use and:

select *
from mytable
where starts <= '2016-04-16' and '2016-04-16' <= ends;

or between:

select *
from mytable
where '2016-04-16' between starts and ends;

answered Apr 13, 2016 at 18:11

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rogerio Azevedo · Accepted Answer · 2016-04-13 18:49:45Z

0

Do you need events on fixed date?

  select * from yourTable
  where STARTS between '2015-01-01 00:00:00' and '2015-01-01 23:59:59'
  or ENDS between '2015-01-01 00:00:00' and '2015-01-01 23:59:59'

EDITED, try this:

 select * from yourTable
  where STARTS >='2016-04-16' and ENDS <= '2016-04-16'

edited Apr 13, 2016 at 18:49

answered Apr 13, 2016 at 18:13

Rogerio Azevedo

7942 gold badges13 silver badges33 bronze badges

1 Comment

Rogerio Azevedo Over a year ago

Tell me, what exactly you need. Do you have a table with 3 coluns right? ID, STARTS and ENDS and you want to list only events that start or end on single date? Because in your exemple, to show ids 4 and 5 will show events who heppens on the interval

Collectives™ on Stack Overflow

MySQL query to get rows on specific date

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related