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

Flash Camp Boston
New Blog Design
Pre-Conference Training at cf.Objective()
FireFox 3.6 KTML Editor Fix
I am now a part of the Adobe Community Professionals Group

Recent Comments

FireFox 3.6 KTML Editor Fix
Fred said: Found another bug in Firefox 3.6 When inserting a table you can't select the number of columns. So I... [More]

Repeating Events Question
ueghbxedu said: UaejcB <a href="http://ysyhrmkbkhco.com/&...;, [url=http://pwncz... [More]

Repeating Events Question
fadxkfyuadn said: n6qVCL <a href="http://bdiorhdtbwzb.com/&...;, [url=http://uvnao... [More]

FireFox 3.6 KTML Editor Fix
Joshua said: While changing that far will load the editor, does it show the drop down class menu correctly now? [More]

FireFox 3.6 KTML Editor Fix
Al Johnson said: HI, I am still fighting to keep my code going as there is nothing better than KTML nad I have writt... [More]

Calendar

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

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 (18) [RSS]
RPM (9) [RSS]
Spam (16) [RSS]
Technology (66) [RSS]
Testing (3) [RSS]
TV (15) [RSS]
video (32) [RSS]
Web Dev (218) [RSS]
World of Warcraft (16) [RSS]