How to determine if one timespan crosses another

Question

How to determine if one timespan crosses another

waynee.pfeffer

SSC Enthusiast

Points: 135
More actions
January 26, 2011 at 2:25 pm

#233411

I am trying to write up a scheduling system and am currently stuck on how to determine if two timespans intersect. For example:
I schedule something from 3:00 PM - 4:00 PM and later want to schedule something from 1:00 PM - 7:00 PM. How can I easily determine that the two timespans cross and therefore the event shouldn't be scheduled?
The table looks something like:
ID int PK
Name varchar(50)
StartTime datetime
EndTime datetime
Any ideas? I'm sure this is something simple and I'm just overlooking something.
Thank you.
Wayne E. Pfeffer

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Here's one solution including a complete test setup:

You'd need to decide how to deal with timespans that end/begin exactly at the beginning/end of the previous/next timespan. Adjust the < to <= and/or > to >= where needed.

As a side note:

I'm not sure if you're planning to use only the time part of the datetime value. I'd vote against it since you won't be able to design "alternative scenarios"....

DECLARE @tbl TABLE

(

ID INT,

Name VARCHAR(50),

StartTime DATETIME,

EndTime DATETIME

)

INSERT INTO @tbl

SELECT 1,'t1','03:00 PM','04:00 PM'

SELECT *

FROM @tbl

DECLARE

@StartTime DATETIME,

@EndTime DATETIME

SELECT

@StartTime = '01:00 PM',

@EndTime ='07:00 PM'

IF EXISTS (SELECT 1 FROM @tbl WHERE StartTime < @EndTime AND EndTime > @StartTime)

SELECT 'no'

ELSE SELECT 'yes'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

waynee.pfeffer SSC Enthusiast Points: 135 More actions · Answer 2

Well that's embarassing ... I should've known that. Thank you.