CODEWITHSUNDEEP
sqlsql-server-2012
mortenstarck
mortenstarck

Reputation: 2803

Get record with two datetimes

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: enter image description here

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.

Upvotes: 0

Views: 46

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 1

Skorpioh
Skorpioh

Reputation: 1355

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)

Upvotes: 1

Related Questions