SQL - Selecting all rows between 4 dates

Question

id        date_start        date_end
1         2018-01-01        2018-03-31
2         2018-04-01        2018-06-30
3         2018-07-01        2018-09-30
4         2018-10-01        2018-12-31

As for input I have time_start and time_end. How can I select all rows between those dates by comparing time_start and time_end with both date_start and date_end?

Thank you.

if we give you input as `time_start = 2018-03-01` and `time_end = 2018-05-01` what exactly you should get — Veljko89, Apr 18 '18 at 14:49
so it is required that at least one of the input dates are between your column dates .. just go with `where time_start between date_start and date_end or time_end between date_start and date_end` you should be good — Veljko89, Apr 18 '18 at 14:54
Please explain the logic by which the comparison is performed. Anything where the range given by `time_*` has any overlap with that given by `date_*`? Just saying you want to compare them is not sufficient. And it does not make sense for something to be "between 4 dates". — underscore_d, Apr 18 '18 at 14:55
So, for example if we had `time_start = 2018-02-01` and `time_end = 2019-01-01` the query should return any rows that have `date_start` and `date_end` included in `time_start - time_end` range. In our case all 4 rows should be returned. — VladC, Apr 18 '18 at 14:58
pff that's some logic turned up side down man ... idea is that you compare input field with something you have in DB and in your case you are comparing your DB data with input ... `where date_start between time_start and time_end or date_end between time_start and time_end ` — Veljko89, Apr 18 '18 at 15:01
before using between check this https://stackoverflow.com/questions/1630239/sql-between-vs-and — M.A.Bell, Apr 18 '18 at 15:08
@Veljko89 And why is comparing data from database with input wrong or uspide down? I know it "sounds" wrong, but imo it isn't — VladC, Apr 18 '18 at 16:04
@VladC well it will work, ofc it will work and give you result back as requested, but as you noticed every person who checked out question thought you will check if `time_start` is between table columns, but you are doing it other way around .. kinda only reason why I called it `upside down` — Veljko89, Apr 19 '18 at 08:13

Gordon Linoff · Answer 1 · 2018-04-18T23:17:17.537

1

What do you mean by "between"? The rest of this assumes that start times are on or before ends.

One definition is that the entire "time" period is during the period.

select t.*
from t
where @time_start >= date_start and @time_end <= date_end;

Another is that the periods overlap at all:

select t.*
from t
where @time_end >= date_start and @time_start <= date_end;

edited Apr 18 '18 at 23:17

answered Apr 18 '18 at 15:00

Gordon Linoff

1,242,037
58
646
786

I was just putting something like this together .. I think this is best response so far .. since it's the only one that discusses these "side cases". – Ditto Apr 18 '18 at 15:01
@Ditto, like most of the answers this doesn't take into account "edge touch cases". I mean date_end and time_start is same, it is considered an overlap (which likely is not in most cases). For example, I could be able make check in on the same day someone else checks out in a hotel. But this approach considers the room is also occupied on the date_end. – Cetin Basoz Apr 18 '18 at 15:09

Daniel Marcus · Accepted Answer · 2018-04-18T14:55:19.917

0

Is this what you want?

select * from yourtable where 
time_start between date_start and date_end or time_end between date_start and date_end

edited Apr 18 '18 at 14:55

answered Apr 18 '18 at 14:52

Daniel Marcus

2,686
1
7
13

None of those 4 names are used in the OP's example. – underscore_d Apr 18 '18 at 14:54
Unfortunately, no. **How can I select all rows between those dates by comparing time_start and time_end with both date_start and date_end?** – VladC Apr 18 '18 at 14:54
Edited answer is correct, just replace `time_start` with `date_start`. `SELECT * FROM table WHERE date_start BETWEEN time_start AND time_end OR date_end BETWEEN time_start AND time_end` – VladC Apr 18 '18 at 15:03

score 0 · Answer 3 · answered Apr 18 '18 at 14:59

0

I am assuming both the input dates should be between the date_ fields.

select * from whatever 
 where  time_start between date_start and date_end 
 and time_end between date_start and date_end

answered Apr 18 '18 at 14:59

SouravA

5,147
2
24
49

score 0 · Answer 4 · answered Apr 18 '18 at 15:01

Select * from myTable
Where time_start < date_end and time_end > date_start

Would give you overlapping dates. You would adjust the < and > as per your need. For example, if date_end is 2018-05-06 and your time_start is also 2018-05-06 then to include this you would use:

... time_start <= date_end

If like a reservation, these date_end, time_start values aren't considered an overlap (one checks out that day, another one checks in) then:

... time_start < date_end

HTH

SQL - Selecting all rows between 4 dates

4 Answers4