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.
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
Like this entry? Subscribe to my blog.
Comments (2) - 637 views
Comments (moderation on)
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.