Well, Jochem, has already given you some valuable information but since
Month() and Year() are standard SQL functions, this should work, too:
where month(datefield) = #form.month#
and year(datefield) = #form.year#
~Dina
----- Original Message -----
From: Jochem van Dieten
To: CF-Talk
Sent: Wednesday, December 11, 2002 9:44 AM
Subject: RE: Another Newbie Problem
Quoting [EMAIL PROTECTED]:
>
> We have several thousand records in a db with a standard odbc
> datetime stamp column. We want to be able to filter out end of month
> statistics (all trouble calls in November, 2002, for instance) by
> using a set of drop down menus (month, year, type of call).
There are 2 generic ways to do this:
- create a startdate and an enddate and do a BETWEEN search
- use SQL date functions to extract year and month from the field and do
an exact search on that
Code example of the latter (the former has been posted already):
SELECT *
FROM table
WHERE EXTRACT(YEAR FROM datefield) = #year#
AND EXTRACT(MONTH FROM datefield) = #month#
(If your database hasn't implemented SQL date functions the exact syntax
might be different and you should naturally use cfqueryparam.)
The question which approach is most efficient is highly dependent on the
database you are using, and especially the index functionality. I would
probably create some indexes and do timings on them. Specifically:
CREATE INDEX test1_idx ON table (datefield);
CREATE INDEX test2_idx ON table (EXTRACT(YEAR FROM datefield),
EXTRACT(MONTH FROM datefield), calltype);
If your database supports it, play around with index types (try an RTREE
for test1_idx). But unless you have millions of records, speed
difference is probably neglegible, and you should go for the strategy
that uses the smallest index.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
