If I were you, I would store your dates and times in the db as a datetime
field. Then it will become much easier to do the where statement.
If you just want to find out if there is an overlap then you could use.
<cfquery name="qGetCount" dataSource="yourDS">
select count(identityField) as overlap
from yourTable
where shiftType = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#formType#">
and ((startDate >= <cfqueryparam cfsqltype="cf_sql_date"
value="#formStartDate#">
and startDate <= <cfqueryparam cfsqltype="cf_sql_date"
value="#formEndDate#">)
or (endDate >= <cfqueryparam cfsqltype="cf_sql_date"
value="#formStartDate#">
and endDate <= <cfqueryparam cfsqltype="cf_sql_date"
value="#formEndDate#">)
</cfquery>
<cfif not qGetCount.overlap>
<!--- Database Insert code here --->
<cfelse>
<!--- Return to or display form here --->
</cfif>
Of course you will have to make sure that your form dates and times conform
to your database. You wouldn't want the user to select 5 (meaning 5pm) and
have come in as 5 am.
Let me know if you need more clarification.
Steve
-----Original Message-----
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:03 AM
To: CF-Talk
Subject: Re: Date Range Query Issues
Hey Jochem :)
1 - Do you mean natively or the way I have the db set up? If it's the
latter, I don't have any constraints for overlaps in the db design. Each
"shift" or date range is its own record. The id is an identity field.
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org <http://www.childrensdayton.org>
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 12/2/2003 9:57:43 AM >>>
>
> The problems are:
>
> 1) I can't figure out what should be in the WHERE clause.
Does your database support OVERLAPS?
> 2) Have I screwed myself by chopping up the dates?
Screwed is not a nice word, but you did make things difficult for
yourself.
Jochem
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
