I am having start time and end time as two fields in my database of type Datetime, Now i want that start time and end time selected by user should not fall within the interval of start time and end time that are already present in database. How to do this validation.
+3
A:
Firstly, ensure that yourStartTime < yourEndTime, if you haven't already.
Then you can run this query on your database:
SELECT COUNT (*) FROM [table]
WHERE [table].EndTime > yourStartTime
AND [table].StartTime < yourEndTime
If the count is non-zero, you've failed validation.
Dan Puzey
2010-03-12 12:47:14
Add some brackets around the * :)
Neurofluxation
2010-03-12 13:04:41
Bah, I knew I couldn't remember my SQL properly :-D Thx!
Dan Puzey
2010-03-12 13:13:55
Now why was this voted down? :-|
Dan Puzey
2010-03-12 20:47:18
+2
A:
You could use TimeSpan ts = endTime.Subtract(startTime); which will give you the difference between the 2 dates. Then, selecting the results from the database you can use the DATEDIFF method in order to compare these values.
If I have understood the question correctly.
Ardman
2010-03-12 12:49:12
You get some nice methods which you can use in order to workout how many Days, Hours, Minutes etc. have passed.
Ardman
2010-03-12 12:57:10
Yes, but I don't think there's a relevance between the duration between two dates, and whether or not the range between them overlaps.
Dan Puzey
2010-03-12 13:07:10