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]
- Re: Date Range Query Issues Jochem van Dieten
- Re: Date Range Query Issues Mickael
- Re: Date Range Query Issues Jochem van Dieten
- RE: Date Range Query Issues DURETTE, STEVEN J (AIT)
- Re: Date Range Query Issues Candace Cottrell
- RE: Date Range Query Issues Tony Weeg
- Re: Date Range Query Issues Jochem van Dieten
- RE: Date Range Query Issues Tony Weeg
- Re: Date Range Query Issues Jochem van Dieten
- RE: Date Range Query Issues DURETTE, STEVEN J (AIT)
- RE: Date Range Query Issues Jochem van Dieten
- RE: Date Range Query Issues Candace Cottrell
- RE: Date Range Query Issues DURETTE, STEVEN J (AIT)
- Re: Date Range Query Issues Jochem van Dieten
- Re: Date Range Query Issues Jochem van Dieten
- RE: Date Range Query Issues DURETTE, STEVEN J (AIT)
- RE: Date Range Query Issues DURETTE, STEVEN J (AIT)
- Re: Date Range Query Issues Jochem van Dieten
- RE: Date Range Query Issues DURETTE, STEVEN J (AIT)
- RE: Date Range Query Issues Candace Cottrell