Link to home
Create AccountLog in
Avatar of MariaHalt
MariaHaltFlag for United States of America

asked on

query for a data range

Say you have a table like this:

CREATE TABLE tempdb.dbo.mh_events (
event_id int not null,
event_startdate datetime not null,
event_enddate datetime not null)

INSERT INTO tempdb.dbo.mh_events VALUES (10, '7/1/2012 12:00AM', '8/15/2012 11:59:59PM')
INSERT INTO tempdb.dbo.mh_events VALUES (20, '7/15/2012 12:00AM', '9/1/2012 11:59:59PM')
INSERT INTO tempdb.dbo.mh_events VALUES (30, '8/1/2012 12:00AM', '9/15/2012 11:59:59PM')
INSERT INTO tempdb.dbo.mh_events VALUES (40, '8/15/2012 12:00AM', '10/1/2012 11:59:59PM')
INSERT INTO tempdb.dbo.mh_events VALUES (50, '9/1/2012 12:00AM', '10/15/2012 11:59:59PM')
INSERT INTO tempdb.dbo.mh_events VALUES (60, '9/15/2012 12:00AM', '11/1/2012 11:59:59PM')
INSERT INTO tempdb.dbo.mh_events VALUES (70, '10/1/2012 12:00AM', '11/15/2012 11:59:59PM')

And you need to get events that were in progress
between 9/1/2012 12:00AM AND 9/30/2012 11:59:59PM.

So you write a query like this:

SELECT * FROM tempdb.dbo.mh_events
WHERE event_startdate BETWEEN '9/1/2012 12:00AM' AND '9/30/2012 11:59:59PM'
OR event_enddate BETWEEN '9/1/2012 12:00AM' AND '9/30/2012 11:59:59PM'

But then you notice that event_id = 40 is not included in your results:

event_id      event_startdate                  event_enddate
20            2012-07-15 00:00:00.000      2012-09-01 23:59:59.000
30            2012-08-01 00:00:00.000      2012-09-15 23:59:59.000
50            2012-09-01 00:00:00.000      2012-10-15 23:59:59.000
60            2012-09-15 00:00:00.000      2012-11-01 23:59:59.000

Which, when I look at my query more closely, makes sense that it's not there.  But I need it to be included because the event was in progress during the month of September.

So, I try again and my lunch coma is setting in...

SELECT * FROM tempdb.dbo.mh_events
WHERE (event_startdate BETWEEN '9/1/2012 12:00AM' AND '9/30/2012 11:59:59PM' OR event_enddate BETWEEN '9/1/2012 12:00AM' AND '9/30/2012 11:59:59PM')
OR (what?  zzzzz )

(burying my head in shame)
Avatar of MariaHalt
MariaHalt
Flag of United States of America image

ASKER

--This seems to work, but I'm still interested in alternatives.

SELECT * FROM tempdb.dbo.mh_events
WHERE (
('9/1/2012 12:00AM' BETWEEN event_startdate AND event_enddate)
OR
('9/30/2012 11:59:59PM' BETWEEN event_startdate AND event_enddate)
)
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you.