CODEWITHSUNDEEP
phpmysqldate
Bilal Rabbi
Bilal Rabbi

Reputation: 1840

How to show events between a start and end date by selecting any date in between

Event A is happening between 24th to 29th date of a month. If user select any date after 24 and before 29. Is there a way to show Event A

I am using this query if user select 25th and 27th.

Select * 
from Events 
where DateFrom >= '25-01-2019' and DateTo <= '27-01-2019'

It will not show Event A because its start date and end date are different.

Example Data

 id  name             DateFrom           DateTo 
 1   Music eventA     2019-01-24       2019-01-29
 2   Music eventB     2019-01-25        2019-01-30

If user select DateFrom '2019-01-26' and DateTo '2019-01-27' I want to show event with id=1 as 26 and 27 lies between 24 to 29

Upvotes: 1

Views: 252

Answers (3)

shahsani
shahsani

Reputation: 699

The selected start date (2019-01-26) should be compared with the DateFrom of the data, while the selected end date (2019-01-27) should be compared with the DateTo, as mentioned in this query,

SELECT * 
FROM Events 
WHERE DateFrom <= '2019-01-26' 
AND DateTo >= '2019-01-27';

Using the following sample data, its working correct,

id  name                DateFrom    DateTo
1   Some other event    2018-12-12  2018-12-31
2   Music Event A       2019-01-24  2019-01-29
3   Music eventB        2019-01-25  2019-01-30
4   Event C             2019-02-01  2019-02-09

The query above returns this,

id  name            DateFrom    DateTo
2   Music Event A   2019-01-24  2019-01-29
3   Music eventB    2019-01-25  2019-01-30

Hope it answers your question!

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

If you want to select all events that "happen" or "partially happen" in your selected range of dates you could do

Select * from Events 
where DateFrom <= '2019-01-27' and DateTo  >= '2019-01-25'

In case you only want those events that are happing in the full span of your specified dates you need to do

Select * from Events 
where DateFrom <= '2019-01-25' and DateTo  >= '2019-01-27'

Instead of using the string format '2019-01-25' you could also use the numeric date format 20190125. Both forms should work equally well.

Upvotes: 0

Umar Abdullah
Umar Abdullah

Reputation: 1290

Please update your query as below. Dates are not passed correctly in the query.

Select * 
from Events 
where ( DateFrom >= '25-01-2019' and DateFrom <= '27-01-2019') and 
      ( DateTo >= '25-01-2019' and DateTo <= '27-01-2019' ).

Upvotes: 0

Related Questions