Q:

SQL Server date/time overlapping check for event scheduling

I have a event table that has the following columns:
StartDate datetime
EndDate datetime
StartTime nvarchar(10)
EndTime nvarchar(10)

Let's say a record has these values in the table:
StartDate: 2011-12-22 00:00:00.000
EndDate: 2011-12-22 00:00:00.000
StartTime: 4:00 PM
EndTime: 5:00 PM

Now, if someone comes to schedule an event, I need to make sure he can't schedule it for the same time or between the start and end times of this existing event.
So, he can schedule from 5:00 PM to 6:00 PM or from 11:00 AM to 4:00 PM but not 11:00 AM to 5:00 PM or 4:30 PM to 5:00 PM.
How can I check for this?  I know this isn't the best design for this table, but I have to stick to this design, else we will have to change a lot of middle layer and client side code.

A:

When adding new record or modifying existing you chould check that your start or end timestamps not falling inside already existing time ranges and new range does not cover the existing.
Assuming that date also matters, not only time, then better way to store begin and end dates and times as DATETIME columns, for example StartDT and EndDT - in this case you have to check non-overlapping other rows with this statement
Where not exists(
    select * from yourtable 
    where (@newstart >= StartDT AND @newstart < EndDT) 
    OR (@newend > StartDT AND @newend <= EndDT) 
    OR (@newstart <= StartDT AND @newend >= EndDT)
)

Of course you can rewrite it as EXISTS clause instead of NON EXISTS
So, if you cannot change the schema, you should simply change StartDT and EndDT to the expressions, which compose corresponding values from the underlying string columns, for example
 StartDT = SUBSTRING(StartDate, 1, 10) + ' '+ StartTime

A:

Try this
declare @apptDate DATETIME

SET @apptDate = '12/22/2011 4:30PM'
select COUNT(*) 
from t1
where @apptDate Between
CAST( StartDate+StartTime as DateTime) and CAST( EndDate+EndTime as DateTime)  

SET @apptDate = '12/22/2011 6:30PM'
select COUNT(*) 
from t1
where @apptDate Between
CAST( StartDate+StartTime as DateTime) and CAST( EndDate+EndTime as DateTime)  

If count(*) > 0, an appointment exists.   If not, the spot is free

