We help IT Professionals succeed at work.

Comparing date times start time and end time using between

clintnash
clintnash asked
on
389 Views
Last Modified: 2012-05-10
I cant seem to get the right combination to generate the proper results.  I need to compare the start date and end date to determine if rows are overlaping.  Example

RowID        StartTime                           EndTime
1                9/11/2010  08:00:00         9/11/2010  09:00:00
2                9/11/2010  07:30:00         9/11/2010  08:30:00
3                9/11/2010  08:30:00         9/11/2010  09:30:00
4                9/11/2010  09:30:00         9/11/2010  10:30:00

I need to return row 1,2 and 3 but not row 4 since the start time is the same as the end time of the previous appointment. Any help is greatly appreciated.
Comment
Watch Question

dsackerContract ERP Admin/Consultant
CERTIFIED EXPERT

Commented:
Try this:
select RowID,
       StartTime,
       EndTime
from   MyTable
where  StartTime NOT IN (select EndTime from MyTable)

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what about this:
select t.*
 from yourtable t
 where exists( select null from yourtable o where o.starttime < t.endtime and o.endtime > t.starttime )

Open in new window

Software Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Short of a missing ) at the end, this query returns the correct results. Thanks to each of your for your help this morning.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
did you try mine? did it not work?

Author

Commented:
Angel, Actually I tried them all, the first returned two of the three rows, yours returned all four rows.  Thank you...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.