I'm no Oracle expert, but this can do it (maybe there is a better way)
<cfparam name="form.month" default="01">
<cfset startdate = CreateDate(Year(Now()),form.month,"1")>
<cfset enddate = DateAdd("m",1,startdate)>
<CFQUERY DATASOURCE="#Application.DSN#" NAME="updateguide">
SELECT eventid, eventdate, longtitle
FROM whatsonWHERE trunc(eventdate) =
TO_DATE(#DateFormat(startdate,"mm/dd/yyyy")#,"MM/DD/YYYY")
AND trunc(eventdate) <
TO_DATE(#DateFormat(enddate,"mm/dd/yyyy")#,"MM/DD/YYYY")
ORDER BY eventdate
</CFQUERY>
.. [in the page: form to refresh]
<cfoutput>
<form action="#CGI.SCRIPT_NAME#?#CGI.QUERY_STRING#" method="post">
<select name="month" size="1">
<cfloop index="i" from="1" to="12">
<option value="#i#"<cfif i IS form.Month>
SELECTED</cfif>>#MonthAsString(i)#</option>
</cfloop>
</select>
<input type="submit" value="refresh">
</form>
</cfoutput>
-----Original Message-----
From: Ian Vaughan [mailto:[EMAIL PROTECTED]]
Sent: donderdag 17 januari 2002 16:29
To: CF-Talk
Subject: Group queries in Coldfusion by date.
Hi
I have a query that brings back entries from my database, so that users
can
edit them in their web browser.
At present the query returns all records, but I would like to display
the
output generated by date, so for example only January would be shown and
if
entries in the database existing for events in February, March etc they
would be accessible via a drop down form select box.
I.E. The user selects April from the select box and the page is
refreshed
showing only the entries for April and not January.
Any ideas on how to achieve this? or is it achievable.
This is my current code, I am using an Oracle 8 database
<CFQUERY DATASOURCE="#Application.DSN#" NAME="updateguide">
SELECT eventid, eventdate, longtitle
FROM whatson
ORDER BY eventdate
</CFQUERY>
-----------------
<table width="100%" cellpadding="1" cellspacing="1" border="0"
valign="top">
<!--- START --->
<CFOUTPUT QUERY="updateguide">
<tr>
<td width="20%">
<span class="gstext11">#DateFormat(eventdate,"d-mmmm-yyyy")#</span>
</td>
<td width="60%"><span class="gsheader11">#longtitle#</span></td>
<td width="10%" align="center"><a
href="changeeventdetails.cfm?eventid=#eventid#"
class="gsyellow11">EDIT</a></td>
<td width="10%" align="center"><a
href="javascript:confirmDelete('mediadelete.cfm?eventid=#eventid#')"
class="checkprice">DELETE</a></td></tr>
<tr>
<td width="100%" colspan="4" align="center">
<img src="/images/dot_grey.gif"
width="100%" height="1" vspace="2">
</td>
</tr>
</CFOUTPUT>
<!--- END --->
</table>
TIA
Ian
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
