Two Date Values to Select from Two Date Columns Question

What approach would you use to query an events TBL that contains a start and end date when you must query it with your own start and end date?  For example lets say you have a simple events search / display.  You want users to search a span of time so there is two form fields for dates.  Start date and end date. 

The event DB also has two date/time fields.  As each event may span multiple dates.  Some events (like a stage production) may go for a month or more. 

So how would one write a query to get the records?  I have had many variations with little success.  This one is close, but I am not sure it will be 100% accurate.  It is easy enough to get records with just one date as a variable, but when you have two dates and two date columns things are not as easy.  At least not as easy on a sunday night. :-)  If you have a better direction for this I am all ears.  For some reason date/time queries often confuse me.

The first two lines will get most of the dates, but an event say 2 months long will not be picked up if the dates searched are smack in the middle (say today and tomorrow is searched).

The last two lines really just get the forms start date and end date in case there are long events.  But I think I may be missing events that are on the edge but not of those dates.  I really don't want to loop through each possible date in the range and do a between for each one.

EventDayTimeTBL.EventStartday Between <Cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.eventstartdate)#"> AND <Cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.eventenddate)#">


OR EventDayTimeTBL.EventEndday Between <Cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.eventstartdate)#"> AND <Cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.eventenddate)#">


OR <Cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.eventstartdate)#"> Between EventDayTimeTBL.EventStartday AND EventDayTimeTBL.EventEndday


OR <Cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(form.eventenddate)#"> Between EventDayTimeTBL.EventStartday AND EventDayTimeTBL.EventEndday

TweetBacks
Comments
Alex's Gravatar Hi. I did something similar not too long ago. Maybe this pseudo-code might help...

select events where (EventStartTime is less than EndTimeFromForm) and (EventEndTime is greater than StartTimeFromForm)

This seems to get events whose periods partiall overlap the queiried period, as well as those events that are completely contained. If you're just using dates (no times invloved... or they're all 00:00:00), then you might need to use "equals" with the less and greater thans. Let me know if it's helpful... (I'm known for my ability completely misunderstand people :0)
# Posted By Alex | 11/4/07 8:23 PM
Joshua Cyr's Gravatar The problem with that query alex is that lets say we have an event from November 1st to the 15th. Then someone searches a span of time from October 25 to November 5th.

The query wouldn't pick the event even though the spans overlap.
# Posted By Joshua Cyr | 11/5/07 10:36 AM

NAVIGATION

Home
About Me

RSS


Search

Subscribe

Enter your email address to subscribe to this blog.

Recent Entries

Inception Plot Questions
Random Chuck Norris Fact Generator With A Twist
Virtual Currency for Buses
Applying ColdFusion Security Patches Gotcha
Privacy, Walled Gardens, Standards and Our Future

Recent Comments

FireFox 3.6 KTML Editor Fix
rock guitar said: Is the ktml realy discontinued for dreamweaver [More]

FireFox 3.6 KTML Editor Fix
Joshua said: Hi Wayne, I just tried opening the editor in IE from the link you supplied. I was able to open the ... [More]

FireFox 3.6 KTML Editor Fix
wayne said: Hi everybody, thanks for your reply joshua. I still have a bug for ktml in asp for a link properties... [More]

Inception Plot Questions
said: [More]

Inception Plot Questions
Taylor said: Hey there Josh, Great questions all. You seem to think this is more of a Jacob's Ladder equivalent ... [More]

Calendar

Sun Mon Tue Wed Thu Fri Sat
   1234
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30   

Archives By Subject

blogs (31) [RSS]
books (4) [RSS]
Crazy (39) [RSS]
DIY (8) [RSS]
Flex (3) [RSS]
games (10) [RSS]
GRRR (13) [RSS]
Ideas (11) [RSS]
Local (14) [RSS]
LOLpics (2) [RSS]
money (9) [RSS]
music (3) [RSS]
Personal (27) [RSS]
Photos (8) [RSS]
Politics (8) [RSS]
Projects (22) [RSS]
Review (19) [RSS]
RPM (9) [RSS]
Spam (16) [RSS]
Technology (68) [RSS]
Testing (3) [RSS]
TV (15) [RSS]
video (32) [RSS]
Web Dev (224) [RSS]
World of Warcraft (16) [RSS]