How to check a meeting time in C#

Question

I have a meeting time starts from 6.00 AM to 9.00 PM in drop down with drop down value storing as 1 to 6.00 AM and 2 to 6.30 AM...... till 9.00 PM .I have kept drop down value as number since it is difficult to play with AM and PM so in database it stores as number. It has from time and to time 2 drop down... I donot want to have overlapping meeting. Like if 6.00 AM to 9.00 AM is already created. i donot want to create a another meeting from 6.30 AM to 10.00 AM ... How to check a overlapping meeting ... can any one help me... I am using asp.net with c# and SQL server 2005 at back end....

Answer 1

It will be easier using DateTime object; then you can check like following.

1. Check for existing meeting schedules that have start time lesser than new time - takes care of 6.30 AM when you have one meeting scheduled at 6 AM.

   1. If the start time is fine then look for end time not greater than any start time takes are of the scenario - 5AM start and end time is after another scheduled meeting (with 6 AM one scheduled).

if these criteria are met then you can allow new meeting to be scheduled.

Answer 2

I check it with this SQL:

select count(*) from Appointment_data 
where 
  (ad_time_to > @SelectedTimeFrom) and (ad_time_from < @ SelectedTimeTo)

This query has 0 result, if not meeting at same time.

Answer 3

As per others. Use of DateTime object would be better, but also I would add that you should stick to UTC for all calculations and storage and convert to local time for user interaction. That way you need not worry about timezone changes, leap days or changes to and from summer time.

Answer 4

I would probably have the table as such:

Meetings
--------
Unique_ID
Meeting_Date
Start_Time
End_Time

Then I would create a stored procedure that retrieved all meetings that where within a specific date range i.e.

Select Count(*) from Meetings where Meeting_Date=@date and Start_Time >= @startTime and Start_Time < @endTime Or End_Time > @startTime and End_Time < @endTime

If from this query you return any results then you know the entered times either overlap an existing meeting, or they clash with another.