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

Mar 19 2013 11:58 PM
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.