The problem with your change is that it won't pick up partial overlays.
Mine works if you consider a few things.
1) the startdate and enddate have to be fully qualified datetimes.
2) there was one error an extra set of () needed to be included.
So this works:
select count(idField)
from yourTable
where type = 'yourtype'
and ((startDateField between '12/01/2003 00:00:00' and '12/31/2003
23:59:59')
OR
(endDateField between '12/01/2003 00:00:00' and '12/31/2003
23:59:59'))
The modified version that you had below had an and in it between the 2 date
tests which would mean that it wouldn't pick up overlays like ("11-25-2003",
"12-15-2003") & ("12-01-2003", "12-31-2003")
If this doesn't work for you, let me know because it works for me.
Steve
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 3:31 PM
To: CF-Talk
Subject: Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote:
>
> But you can use between
>
> select count(idField)
> from yourtable
> where type = 'checktype'
> and (startDateField between 'startdate' and 'enddate'
> OR endDateField between 'startdate and 'enddate')
That won't work, it won't detect the following overlap:
('2003-12-01', '2003-12-31') & ('2003-12-02', '2003-12-30')
You need a statement that is actually even simpler:
SELECT COUNT(idField)
FROM yourtable
WHERE type = 'checktype'
AND startDateField < <cfqueryparam cfsqltype="cf_sql_date"
value="#formEndDate#">
AND endDateField > <cfqueryparam cfsqltype="cf_sql_date"
value="#formStartDate#">
Just make sure you verify that the user submitted startdate is
less than the user submitted enddate.
Jochem
--
Who needs virtual reality
if you can just dream?
- Loesje
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
