Here's how I would do it:
Given two form variables, Form.dataMonth and Form.dataYear I would create
the start and end dates and use those:
<cfset startDate=CreateDate(Form.dataYear, Form.dataMonth, 1)>
<cfset endDate=CreateDate(Form.dataYear, Form.dataMonth,
DaysInMonth(startDate))>
<cfquery name="qDateRange" datasource="#DSN#">
SELECT
COLUMN1, COLUMN2, COLUMN3
FROM
TABLENAME
WHERE
DATECOLUMN >= #CreateODBCDate(startDate)#
AND
DATECOLUMN <= #CreateODBCDate(endDate)#
</cfquery>
Depending on how your database stores dates you may be better off
incrementing the endDate by one day and using less than instead of less than
or equal to. This is because the endDate is midnight of that date. Anything
where the timestamp is after midnight the same day (e.g., 11/30/2002
09:00:00) won't be found. If you're only looking for months, you could do
something like this:
<cfset endDate=DateAdd("m",1,startDate)>
Then your WHERE clause would look like this:
DATECOLUMN >= #CreateODBCDate(startDate)#
AND
DATECOLUMN < #CreateODBCDate(endDate)#
If you don't want to be locked in to just looking at a month:
<cfset endDate=DateAdd("d",1,CreateDate(Form.dataYear, Form.dataMonth,
DaysInMonth(startDate)))>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, December 11, 2002 6:31 AM
> To: CF-Talk
> Subject: Another Newbie Problem
>
>
> In all my cfm references I cannot find a single example of a
> common task, although you see this kind of thing being used
> everywhere.
>
> What I want to do is select from a pair of drop down lists
> (like Month and Year) and then display only the records from
> November 2002, for instance.
>
> How do you pass just those two variables to a query and draw
> just the date and month from a standard db date/time string.
> Is it a datepart function?
>
> I just don't get it. Any insight or pointing toward a
> tutorial would be appreciated.
>
> humbled by cfm yet again....
>
> Rick
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
