
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

NAVIGATION
HomeAbout Me
RSS
Search
Subscribe
Recent Entries
Flash Camp BostonNew 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 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 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 | 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]

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)
The query wouldn't pick the event even though the spans overlap.