CODEWITHSUNDEEP
sqlsql-servert-sql
loveprogramming
loveprogramming

Reputation: 578

Select between two dates when there is overlapping

I have these data in my view

ID       event_start               event_end           
499 2021-07-13 00:00:00.000 2021-07-13 00:00:00.000 
499 2021-07-15 00:00:00.000 2021-07-15 00:00:00.000 
499 2021-07-14 05:00:00.000 2021-07-14 06:00:00.000 
499 2021-07-14 02:00:00.000 2021-07-14 03:00:00.000 
499 2021-07-14 07:00:00.000 2021-07-14 09:00:00.000

So I have parameters as @date_start and @date_end and would like to select like this:

DECLARE @date_start datetime2 = '2021-07-14 07:00', @date_end datetime2 = '2021-07-14 08:10'

Select ID, t1.event_start, t1.event_end from Table1 
WHERE event_start >= @date_start AND event_end <= @date_end
AND ID=499

That will give me no returned result. But my goal is to get the last row.

499 2021-07-14 07:00:00.000 2021-07-14 09:00:00.000

since this row starts at 7 and finishes at 9 which will cover the @date_start and @date_end period. How could I do that?

Also if I changed @date_start and @date_end to this:

DECLARE @date_start datetime2 = '2021-07-14', @date_end datetime2 = '2021-07-14'

It should return

499 2021-07-14 05:00:00.000 2021-07-14 06:00:00.000 
499 2021-07-14 02:00:00.000 2021-07-14 03:00:00.000 
499 2021-07-14 07:00:00.000 2021-07-14 09:00:00.000

because all three row happened on the same day but just different time.

Thank you

Upvotes: 0

Views: 115

Answers (2)

Charlieface
Charlieface

Reputation: 71159

It seems you actually want to check for any overlaps of the date ranges in the table with the date range specified in the parameters.

Assuming that it doesn't matter if two intervals butt-end each other (say 07:00-08:00 and 08:00-09:00), you need to do an interval check like this start1 < end2 AND end1 > start2

DECLARE @date_start datetime2 = '2021-07-14 07:00', @date_end datetime2 = '2021-07-14 08:10'

SELECT
    ID,
    t1.event_start,
    t1.event_end
FROM Table1 
WHERE event_start < @date_end AND event_end > @date_start
  AND ID = 499;

Upvotes: 1

Aleix CC
Aleix CC

Reputation: 2079

First thing to bear in mind, are your datetimes in DATETIME type?

If so, let's check the first case.

1.To see whether a datetime/timestamp is contained in a range, we specify the following WHERE clause:

WHERE 
  event_start <= '2021-07-14 07:00:00' and
  event_end >= '2021-07-14 08:10:00'

1. OUTPUT

id  event_start             event_end
--- ----------------------- -----------------------
499 2021-07-14 07:00:00.000 2021-07-14 09:00:00.000

In case your data is not in DATETIME/TIMESTAMP type, this might not work.

2.Here, you change the condition, since you want all the stuff that happened during a whole day. Therefore, the WHERE clause looks like this:

WHERE 
  event_start >= '2021-07-14' and
  event_end < '2021-07-15'

2. OUTPUT

id  event_start             event_end
--- ----------------------- -----------------------
499 2021-07-14 05:00:00.000 2021-07-14 06:00:00.000
499 2021-07-14 02:00:00.000 2021-07-14 03:00:00.000
499 2021-07-14 07:00:00.000 2021-07-14 09:00:00.000

Hope it helped!

Upvotes: 0

Related Questions