如何在 SQL Server 中确定日期时间范围是否与另一个重叠 [英] How can I determine in SQL Server if a dateTime range overlaps another

问题描述

我们有例如.下表

| ID | startDateTime       | endDateTime         |
+----+---------------------+---------------------|
|  1 | 2010-01-01 10:30:00 | 2010-01-01 11:00:00 |
|  2 | 2010-01-01 10:30:00 | 2010-01-01 11:30:00 |
|  3 | 2010-01-01 11:00:00 | 2010-01-01 11:30:00 |
|  4 | 2010-01-01 11:00:00 | 2010-01-01 12:00:00 |
|  5 | 2010-01-01 11:30:00 | 2010-01-01 12:00:00 |
|  6 | 2010-01-01 10:30:00 | 2010-01-01 12:00:00 |

我会例如.查询与

@startDateTime 2010-01-01 11:00:00
@endDateTime 2010-01-01 11:30:00

我想获得以下 ID:2、3、4、6

I want to get following IDs: 2, 3, 4, 6

我的大脑很古怪，无法产生正确的陈述...... :(

My brain is way to wacky to produce the correct statement ... :(

推荐答案

你可以这样做

declare @T table (ID int, startDateTime datetime, endDateTime datetime)

insert into @T values
(  1 , '2010-01-01 10:30:00' , '2010-01-01 11:00:00'),
(  2 , '2010-01-01 10:30:00' , '2010-01-01 11:30:00'),
(  3 , '2010-01-01 11:00:00' , '2010-01-01 11:30:00'),
(  4 , '2010-01-01 11:00:00' , '2010-01-01 12:00:00'),
(  5 , '2010-01-01 11:30:00' , '2010-01-01 12:00:00')

declare @startDateTime datetime
declare @endDateTime datetime


set @startDateTime = '2010-01-01 11:00:00'
set @endDateTime = '2010-01-01 11:30:00'

select *
from @T
where
  startDateTime < @endDateTime and
  endDateTime > @startDateTime

这篇关于如何在 SQL Server 中确定日期时间范围是否与另一个重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！