>Sounds like you are retrieving data and then filtering on the client with CF
>based date math. And if so, then the best advice I can give you is don't.
>This is the type of thing you need to do at the database level (in a SELECT
>statement or a view or a stored procedure etc.). DBMSs are really good at
>this type of date calculation, and you could easily create calculated fields
>with all of the expiration values calculated automatically, and then you
>just SELECT the ones with expired dates.
>
>--- Ben
Thanks Ben. I've been reading your Coldfusion 8 books, they're very good btw!
How would I do that? Here's what my CFC looks like now:
<!--- Get the dates for the report generator loop --->
<cffunction name="getExpirationDate" returntype="query"><br>
<!--- The where checks to see if it is a recent date
this ties into the generator.cfm page. It prevents
the database from outputting data older the the 2008
espically if there is an emply row.
Will do validation on the Generator.cfm to display and pass
to the report to only show dates with a valid date. 1900
is the SQL server's default 0 value. Filtering this out will
give us an accurate report both on the SQL end but also needed
on the CFM end.
--->
<cfquery datasource="#DSN#" name="getExpirationDate">
SELECT
profileID,
dateDiff(day,getDate(),cprAdultExp) as adultExp,
dateDiff(day,getDate(),cprInfantChildExp) as kidExp,
dateDiff(day,getDate(),cprFPRExp) as frpExp,
dateDiff(day,getDate(),aedExp) as aExp,
dateDiff(day,getDate(),firstAidExp) as aidExp,
dateDiff(day,getDate(),emtExp) as eExp,
dateDiff(day,getDate(),waterSafetyInstructionExp) as waterExpt,
dateDiff(day,getDate(),bloodPathogensExp) as bloodExp,
dateDiff(day,getDate(),oxygenAdminExp) as oxyExp,
dateDiff(day,getDate(),lifegaurdingExp) as lifeExp,
dateDiff(day,getDate(),wildernessResponderExp) as wildExp,
certNotes
FROM
mod_studentCertifications
WHERE
cprAdultExp>'2008-01-01'
or
cprInfantChildExp>'2008-01-01'
or
cprFPRExp>'2008-01-01'
or
aedExp>'2008-01-01'
or
firstAidExp>'2008-01-01'
or
emtExp>'2008-01-01'
or
waterSafetyInstructionExp>'2008-01-01'
or
bloodPathogensExp>'2008-01-01'
or
oxygenAdminExp>'2008-01-01'
or
lifegaurdingExp>'2008-01-01'
or
wildernessResponderExp>'2008-01-01'
</cfquery>
<cfreturn getExpirationDate>
</cffunction>
<!--- // End get the expiration date --->
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341142
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
