[SQL] to Check whether one date range to another

Good afternoon.
Please help me to create SQL query.

There are two dates.
You need to check that the interval between these data does not fall in the date interval in the database table. And also that date range in the table falls in the interval between these dates.
October 14th 19 at 10:34
4 answers
October 14th 19 at 10:36
well
select * from table t
where 
not
(
{$fromDate} between t.date1 and t.date2
or
{$toDate} between t.date1 and t.date2
)
with the data in the table similarly - ewell_Lindgren commented on October 14th 19 at 10:39
or according to Boolean algebra, you can rewrite differently
select * form table t
where
(not {$fromDate} between t.date1 and t.date2)
and
(not {$toDate} between t.date1 and t.date2)
- ewell_Lindgren commented on October 14th 19 at 10:42
Almost. Forgot the case when the interval (fromDate, toDate) contains (t.date1, t.date2). - micheal_Mo commented on October 14th 19 at 10:45
not forgotten - ewell_Lindgren commented on October 14th 19 at 10:48
in General, if
guaranteed {$fromDate} < {$toDate}
and
t.date1 < t.date2
it
select * form table t
where
{$fromDate} > t.date2
or
{$toDate} < t.date1
<source>
- ewell_Lindgren commented on October 14th 19 at 10:51
Yes protupila with (t.date1, t.date2) inside (fromDate, toDate)
but the latest version solves
possible initial conditions and validation to add - ewell_Lindgren commented on October 14th 19 at 10:54
Yes, the latest version is good. Only there, or NOT add, or invert the condition:
SELECT * FROM table t WHERE {$fromDate} <= t.date2 AND {$toDate} >= t.date1 - micheal_Mo commented on October 14th 19 at 10:57
Don't have anything to invert.
The question stated: "misses". - ewell_Lindgren commented on October 14th 19 at 11:00
Thank you.
Can I ask another question on the same issue.
I have a table view
id date1 date2

Sample data: gyazo.com/6d67a1f3330a9b117043e65e20638d31.png

How can you use that id and two dates from a script, to check intersect whether he dates in the table?

Something like:

SELECT * FROM `order` WHERE `room_id`=99 NOT IN (SELECT room_id FROM `order` AS `o` WHERE ('2010-12-02' BETWEEN o.check_in and o.check_out OR '2010-12-29' BETWEEN o.check_in and o.check_out) AND `room_id`=2)

In the case above is displayed, respectively, room_id=1, since the second selection selects all the room_id for which dates overlap. - alden7 commented on October 14th 19 at 11:03
Yeah. And that will give your option, if intersections on dates yet? Right, all records from the table. - micheal_Mo commented on October 14th 19 at 11:06
So I'm talking about too... - alden7 commented on October 14th 19 at 11:09
October 14th 19 at 10:38
declare date1 datetime;
declare date2 datetime;
set @date1 = '2010-12-03';
set @date2 = '2010-12-07';

select * from table t where t.date not between @date1 and date2;
this is at least the second, the first do or do not understand or it is equivalent to the first - ewell_Lindgren commented on October 14th 19 at 10:41
upd - ewell_Lindgren commented on October 14th 19 at 10:44
well, sobsno, it is
select count(1) from table t where t.date between $minDate and $maxDate;
if count > 0, then intersects - micheal_Mo commented on October 14th 19 at 10:47
October 14th 19 at 10:40
`start` < #end AND `reservation`.`end` > #start
Where #start and #end, respectively, of your range
how to set #start?

they can overlap partially.
for example 20.11.2010 — 11.12.2010 and 9.12.2010 and 23.12.2010 - ewell_Lindgren commented on October 14th 19 at 10:43
So what, everything will work.
#start and #end, respectively, the interval to check, and checked everything in it or its crosses - ewell_Lindgren commented on October 14th 19 at 10:46
October 14th 19 at 10:42
SELECT * FROM dates d WHERE NOT (
 (my_from < d.date_from AND my_to <d.date_from)
OR
 (my_from > d.date_to AND my_to > d.date_to)
)

Find more questions by tags SQL