Hi Julia,
I really appreciate your response. I don't think I did a good enough job
explaining my problem. I think I need something like the opposite of
"between" to do what I want to do here. Again, the problem is that someone
is going to look at one particular day on the calendar at a time. Its very
easy to pick up scheduled events that start or end the day that is being
looked at. The trouble is that some events last for days or weeks. What I
need is some clue as to how to figure out if any "ongoing" events fall on
the day in question. I don't think I can use BETWEEN because how would I
ever know what date to start the between statement with? An event could
have started last week, last month or last year even. This must me a
typical problem, my brain is just a little too small to see the answer at
the moment. For instance:
Say I have an event that starts April 1, 2000 and ends April 15th. A user
comes along and asks to see scheduled events for April 10th. What statement
would I use to find out if that this event does in fact fall on the 10th
(keep in mind that this is just 1 example. My database will probably have
thousands of events starting and ending on various dates.
Eron
-----Original Message-----
From: Julia Green [mailto:[EMAIL PROTECTED]]
Sent: Sunday, April 23, 2000 9:25 PM
To: [EMAIL PROTECTED]
Subject: Re: Working with durations in SQL and CF
Well, first of all, if you want to use any of the DiffDate functions, or
any of the Date Functions in SQL, you must define your fields in Access
as Date fields. You can modify the output later (to exclude the time of day
that appears when you use the date) in CFOUTPUT using DateFormat.
If you want to set up a search between 2 dates on a form, that is enter
a Start Date and a End Date on a form, and then find the date between it,
you have to use the text format in Access.
I am including beneath 2 examples of output pages I used for work, the
second
page, the dates must in Access as Date, in order for it to work, there are
alot
of other Date Functions, a good place to look for them is
www.houseoffusion.com
(good documentation there), the first is how to do a search with dates
defined
as text. You can see a little of the SQL you required there...
<CFQUERY NAME="EmpList" DATASOURCE="0707_acc_finacc_asc">
SELECT LastName, FirstName, SSNo, DateA, DateB, DateC, DateD, DateE, DateF,
DateG
FROM vac20003
WHERE (DateA >='#Form.Date1#' AND DateA <='#Form.Date2#') OR
(DateB >='#Form.Date1#' AND DateB <='#Form.Date2#') OR
(DateC >='#Form.Date1#' AND DateC <='#Form.Date2#') OR
(DateD >='#Form.Date1#' AND DateD <='#Form.Date2#') OR
(DateE >='#Form.Date1#' AND DateE <='#Form.Date2#') OR
(DateF >='#Form.Date1#' AND DateF <='#Form.Date2#') OR
(DateG >='#Form.Date1#' AND DateG <='#Form.Date2#') AND
(OpCtr='#OpCtr#')
ORDER BY LastName ASC
</cfquery>
<HTML>
<HEAD>
<BODY bgcolor="#FFFFFF">
<TITLE>Vacation Search Results by Center</TITLE>
</HEAD>
<CENTER><TABLE BORDER="1" CELLSPACING=4 CELLPADDING=7 WIDTH=670 height="30">
<TR><TD WIDTH="642" VALIGN="CENTER" HEIGHT=45>
<b><i>PAYROLL DEPARTMENT/UPS</i><BR>
<I>15 ARLINGTON STREET, WATERTOWN MA 02472</I></b></TD>
</TR>
</TABLE>
</CENTER>
<HR>
<H1 align="center">Vacation Search Results</H1>
<center>
<p><b>UNITED PARCEL SERVICE - NEW ENGLAND DISTRICTS</b></p>
<p><b>NON-UNION VACATION SCHEDULE</b></p>
<p><b>2000</b></p>
<p> </p>
</center>
<p align="left"><b>Vacations are selected by length of service with
company.</b></p>
<p align="left"><b>Non-union employees can opt to use 1 week in
days.</b></p>
<p align="left"><b>Note in the no of wks column as follows: (3+1) - employee
has
4 week avail. is taking 1 week in days.</b></p>
<p align="left"><b>Legend: X=vacation week</b></p>
<CFOUTPUT QUERY="EmpList">
<b>#LastName# #FirstName#<br> #SSNo#<br>
<b>#DateA# #DateB# #DateC# #DateD# #DateE# #DateF# #DateG#<br>
</CFOUTPUT>
<cfoutput>
#DateFormat(CreateODBCDate(DateA), "mm/dd/yyyy")#
#DateFormat(CreateODBCDate(DateB), "mm/dd/yyyy")#
</cfoutput>
<cfoutput>
<CFIF #Abs(DateDiff("d", DateB, DateA))# less than 22>
<p>These weeks are back to back.
<cfelse>
<p>No Information.
</cfoutput>
</cfif>
<P>
<b>Thank you for searching the Vacation Posting Database!</b></P>
<HR>
</CENTER>
</BODY>
</HTML>
<CFINSERT DATASOURCE="0707_acc_finacc_asc" TABLENAME="vac20003">
<cfoutput>
<CFIF #Abs(DateDiff("d", DateB, DateA))# less than 22>
<p>These weeks are back to back.
<cfelse>
<p>No Information.
</cfif>
</cfoutput>
<h3>Your vacation information has been successfully added to the database.
<p>Please print this out for your records (Legal Paper is Recommended) and
for later reference and lookup from the database search.
</h3>
<HR>
<CFOUTPUT>
<p><b>Date Information Entered: #Date#
<p><b><CFIF Len(LastName) greater than 0>Employee Full Name: #LastName#
#FirstName#
<CFELSE><p><b>Employee Full Name: Information is Not Available.</CFIF>
<p><b><CFIF Len(SSNo) greater than 0><p><b>Social Security No: #SSNo#
<CFELSE><p><b>Social Security No: Information is Not Available.</CFIF>
<p><b><CFIF Len(HireDate) greater than 0><p><b>HireDate: #HireDate#
<CFELSE><p><b>Hire Date: Information is Not Available.</CFIF>
<p><b><CFIF Len(NoWeeks2) greater than 0><p><b>Maximum Number of Weeks:
#NoWeeks2#
<CFELSE><p><b>Maximum Number of Weeks: Information is Not Available.</CFIF>
<p><b><p><b><CFIF Len(District) greater than 0><p><b>District Number:
#District#
<CFELSE><p><b>District: Information is Not Available.</CFIF>
<p><b><CFIF Len(OpCtr) greater than 0><p><b>Operations Center: #OpCtr#
<CFELSE><p><b>Operations Center: Information is Not Available.</CFIF>
<p><p><p><p>
<hr>
<p><b><CFIF Len(NoWeeks) greater than 0><p><b>Number of Weeks Eligible:
#NoWeeks#
<CFELSE><p><b>There are no number of weeks eligible.</CFIF>
<p><b><CFIF Len(NoWeeks) greater than 0><p><b>Number of Weeks Eligible:
#NoWeeks#
<CFELSE><p><b>There are no number of weeks eligible.</CFIF>
<p><b><CFIF Len(DateA) greater than 0>First Vacation Selection: #DateA#
<CFELSE><p><b>Not Applicable</CFIF>
<p><b><p><b><CFIF Len(DateB) greater than 0>2nd Vacation Selection:
#DateB#
<CFELSE><p><b>Not Applicable</CFIF>
<p><b><CFIF Len(DateC) greater than 0>3rd Vacation Selection: #DateC#
<CFELSE><p><b>Not Applicable</CFIF>
<p><b><CFIF Len(DateD) greater than 0>4th Vacation Selection: #DateD#
<CFELSE><p><b>Not Applicable</CFIF>
<p><b><CFIF Len(DateE) greater than 0>5th Vacation Selection: #DateE#
<CFELSE><p><b>Not Applicable</CFIF>
<p><b><CFIF Len(DateF) greater than 0>6th Vacation Selection: #DateF#
<CFELSE><p><b>Not Applicable</CFIF>
<p><b><CFIF Len(DateF) greater than 0>7th Vacation Selection: #DateG#
<CFELSE><p><b>Not Applicable</CFIF>
</cfoutput>
Julia Green
Julia Computer Consulting
http://www.juliagreen.com/
Email: [EMAIL PROTECTED]
Phone: 617-926-3413
FAX: 413-771-0306
----- Original Message -----
From: Eron Cohen <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, April 23, 2000 9:00 PM
Subject: Working with durations in SQL and CF
> Hi Everyone,
>
> I am writing a Calendar/scheduling application in Cold Fusion. My problem
> is that I can't figure out what to put in the SQL WHERE statement to
handle
> a particular case:
>
> Someone may have scheduled a event to last several days, or even weeks.
For
> instance the event could start on April 15 and last until April 20th.
The
> IDEA is that the scheduler only allows the user to VIEW one day at a time.
> The user selects a date and then I query the database to see what events
> take place on that day (even if they started a few days earlier and/or
won't
> end for a few days.) I have no trouble selecting the event if it starts
or
> ends on the particular day in question, but I don't know how to handle
> events that last a few days (esp. when the user is trying to view a day
that
> is in the middle of several day event.)
>
> My table (in an MS Access 2000 database) has these fields:
> Start_Date_Time,
> End_Date_Time,
> Name_Of_event
>
> Can anyone give me any ideas on how to SELECT these events that last
several
> days when I am trying to view a day that's in the middle of the event?
>
> Thank You,
>
> Eron
>
> --------------------------------------------------------------------------
----
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
