RE: Date and Time (Checking if Time is blocked)

Question

I have a problem, given three scenarios:

Database: MS Sql Server 2008

Database Column: StartDateTime and EndDateTime
Database Values:

• StartDateTime1 : 10:00
  
• StartDateTime2 : 11:00
• StartDateTime3 : 09:00

• EndDateTime1 : 11:00
• EndDateTime2 : 12:00
• EndDateTime3 : 13:00

Scene 1 (Using StartDateTime1 and EndDateTime1):

• IF((StartDateTime >= '10:00' AND StartDateTime <= '10:30') OR (EndDateTime >= '10:00' AND EndDateTime <= '10:30'))

(Scene 1: Satisfied)

Scene 2 (Using StartDateTime2 and EndDateTime2):

• IF((StartDateTime >= '11:30' AND StartDateTime <= '12:00') OR (EndDateTime >= '11:30' AND EndDateTime <= '12:00'))

(Scene 2: Satisfied)

Scene 3 (Using StartDateTime3 and EndDateTime3):

• IF((StartDateTime >= '10:00' AND StartDateTime <= '12:00') OR (EndDateTime >= '10:00' AND EndDateTime <= '12:00'))

(Scene 1: Not Satisfied)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

How could i satisfy all three scenarios? How would the Time Given by the system see that the given time is set within a bigger span of time?

Thanks in Advance.