Searching Date Span

Quote :

Posted: 12/02/2004, 4:56 AM
You may also want to consider using SQL similar to that displayed below. This way, you'll also derive those records that "occur" within the date range where the start date and end date span the date range.

In other words, let's say that the user selected January 2005 as the date range.

Scenario 1:
date_start >= '1/1/2005' and date_end <= '1/31/2005'

Record # date_start date_end
1 1/3/2005 1/29/2005

gives you only those records where the start and end date are within that range. If that is your intention, that's fine.

However, consider scenario 2.

Scenario 2
sSQL = "SELECT * FROM Events "
sSQL = sSQL & "WHERE " sSQL = sSQL & "('" & dteToday & "' BETWEEN date_start AND date_end) OR "
sSQL = sSQL & "('" & dteStart & "' BETWEEN date_start AND date_end) OR "
sSQL = sSQL & "('" & dteEnd & "' BETWEEN date_start AND date_end) OR "
sSQL = sSQL & "(date_start BETWEEN '" & dteStart & "' AND '" & dteEnd & "') OR "
sSQL = sSQL & "(date_end BETWEEN '" & dteStart & "' AND '" & dteEnd & "')"

This SQL will not only derive the same records as derived from scenario 1, plus it will also derive records where "Today" is between the date_start and date_end...

Record # date_start date_end
1 1/3/2005 1/29/2005
2 12/9/2004 2/9/2005

In this instance you'll notice that the date range of record 2 falls within the selected date range... it 'spans' that range.

The SQL in each scenario is valid according to the effect desired. Just something to consider as at some stage - each of us hits a snag that requires the SQL similar to that shown in Scenario 2.

We hope this is helpful to you and others.
_________________
www.DataObjx.net[/quote]

  
WHERE show_id = {show}  
AND theater_id = {theatre}  
AND ( start_date <= '{e_date}'  
AND end_date >= '{s_date}' )  
ORDER BY show