Finding Records in Date/Time Range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding Records in Date/Time Range

Author

Topic

westmich
Starting Member

35 Posts

Posted - 2003-08-04 : 13:48:41

Didn't think this would be as difficult as it has been. Basically I have a table of start date/times and end date/times. I want to make sure no more then 3 items are scheduled within a given range, i.e. no more then 3 overlapping at anyone time.

Table Schedule:
ScheduleID | StartDateTime | EndDateTime
1 | 8/5/03 7:30 AM | 8/5/03 9:00AM
2 | 8/5/03 7:45 AM | 8/5/03 9:30AM
3 | 8/5/03 7:00 AM | 8/5/03 8:30AM

Ideally, the query should return all three records since there is an overlap, but I can't seem to get it to work.


Select ScheduleID Where StartDateTime>= #8/5/2003 7:30:00 AM# 
And StartDateTime<=#8/5/2003 8:00:00 AM#;

The date/times in the query is what I am testing to see if I can insert a new schedule record.

BTW, this is an Access query, but since 99% of what I do is in SQL, I think the only difference is in the # vs. '

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-04 : 14:01:03

SELECT ScheduleID, StartDateTime, EndDateTime

FROM MyTable

WHERE StartDateTime <= '8/5/2003 8:00:00 AM'

AND EndDateTime >= '8/5/2003 7:30:00 AM'

westmich
Starting Member

35 Posts

Posted - 2003-08-04 : 16:15:35

Thank-you, sir, you're a genious. I knew it had to be something simple, but I just couldn't get it. I posted to another board first and the folks that replied all came to the conclusion that I was going to have to write a lot work around code in my application (ASP.NET/VB) in order to get it to work. I'm glad I tried posting here before resorting to that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources