Get record with two datetimes

Question

I have an issue where I'm trying to get an specific record where I have two dates to get an record.

The date are as follows:

StartTime: '2018-03-02 09:00:00.000'
EndTime : '2018-03-02 11:00:00.000'

The table looks like this:

From the dates above im trying to get the row number 5. I have tried it like so:

SELECT *
FROM [data].[EmergencyOrders]
WHERE [Workcenter] = @Workcenter
  AND [StartDatetime] > @StartTime
  AND ISNULL([EndDatetime], GETDATE()) < @StopTime

But can't seem to get it to work correctly.

You're not getting record 5 because start time is earlier than 9 o'clock. — Rigerta, Mar 02 '18 at 13:40
The problem im having are that the record are before the time im getting. So some have i need to look where these two dates i have fit into. — mortenstarck, Mar 02 '18 at 13:41
I guess you mean 6th row? 5th row starttime is before your start condition — Juan Carlos Oropeza, Mar 02 '18 at 13:46
I mean with the start and end times shown above i should get the row where EmergencyOrderId are equal to 232. — mortenstarck, Mar 02 '18 at 13:47
yes i know that. But maybee im not clear. From what i write above. Then the order startede at 07:19am and ended at 2:02PM. But im asking what are the order status in the time 09am and 11am. Which in this case are the row number 5 — mortenstarck, Mar 02 '18 at 13:50
I understand now. But as you can imagine that logic explanation is much more clear than `im trying to get the row number 5` — Juan Carlos Oropeza, Mar 02 '18 at 13:53

Juan Carlos Oropeza · Accepted Answer · 2018-03-02T14:02:17.647

If you want to know records containing both StartTime and EndTime then you do

WHERE @StartTime BETWEEN  [StartDatetime] AND ISNULL([EndDatetime], GETDATE())
  AND @EndTime BETWEEN  [StartDatetime] AND ISNULL([EndDatetime], GETDATE())

Now the problem I see is if you data range overlap with two ranges. In that case you will have two status. For that I suggest you calculate overlap ranges and select the last one.

Determine Whether Two Date Ranges Overlap

SELECT TOP 1 *
FROM [data].[EmergencyOrders]
WHERE [Workcenter] = @Workcenter
WHERE @StartTime <=  ISNULL([EndDatetime], GETDATE()) 
  AND @EndTime >=  [StartDatetime] 
ORDER BY EmergencyID DESC

score 1 · Answer 2 · answered Mar 02 '18 at 13:58

I'm guessing you want to get those records which intersect with your date range which means you need something like the below SQL:

SELECT *
FROM [data].[EmergencyOrders]
WHERE
    [Workcenter] = @Workcenter AND
    (([StartDatetime] BETWEEN @StartTime AND @StopTime) OR
     (ISNULL([EndDatetime], GETDATE()) BETWEEN @StartTime AND @StopTime)

Get record with two datetimes

2 Answers2