In case anyone is interested, my brute force solution is below. She
aint pretty, but she works.
<cffunction name="getExchangeFilterDateRanges" returntype="query"
access="public" output="no">
<cfargument name="startDate" type="date" required="yes">
<cfargument name="endDate" type="date" required="yes">
<cfset var q = "">
<cfset var checkYear = year(startDate)>
<cfset q = queryNew("startDate,endDate", "date,date")>
<cfset queryAddRow(q)>
<cfset querySetCell(q, "startDate", arguments.startDate)>
<cfset querySetCell(q, "endDate", year(arguments.startDate) &
"-12-31")>
<cfloop from="#arguments.startDate#" to="#arguments.endDate#"
index="thisDate">
<cfif year(thisDate) neq checkYear>
<cfset queryAddRow(q)>
<cfset querySetCell(q, "startDate", thisDate)>
<cfset querySetCell(q, "endDate", year(thisDate)
& "-12-31")>
<cfset checkYear = year(thisDate)>
</cfif>
</cfloop>
<cfset querySetCell(q, "endDate", arguments.endDate)>
<cfreturn q>
</cffunction>
-----Original Message-----
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 19, 2008 3:16 PM
To: cf-talk
Subject: Preplexing Date Range Problem
I need to create a query object that contains records for a partial, or
entire, year's date range.
For example, I need the query to return something similar to where the
start date could be any date in the middle of the year. The end date is
the last day of the year.
StartDate EndDate
2008-11-19 2008-12-31
2009-01-01 2009-12-31
Or, where the start date is the first day of a given year and the end
date is the last day of the year.
StartDate EndDate
2002-01-01 2002-12-31
2003-01-01 2003-12-31
2004-01-01 2004-12-31
Or, where the start and end date could be in the middle of the year:
StartDate EndDate
2005-03-09 2005-12-31
2006-01-01 2006-12-31
2007-01-01 2007-09-23
I know the absolute start and end dates, but I need to break up the date
ranges so that they do not span more than one year (CFEXCHANGEFILTER
bug).
My first solution is to do a brute force loop over each date and then
check each value for a change in the year. Then, add a new record to
the query object.
However, there should be a more-glamorous solution, right?
Mike
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315626
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
