Woops, didn't see you were using MySQL. Just pass the dates into datediff and it will always return the number of days between them, no need to specify the unit of time you want to compare. So:
SELECT * FROM myTable WHERE DateDiff(aDateTimeCol, <cfqueryparam value="#startDateTime#" cfsqltype="cf_sql_timestamp">) >= 0 AND DateDiff(aDateTimeCol, <cfqueryparam value="#endDateTime#" cfsqltype="cf_sql_timestamp">) <= 0 Dominic On 07/02/2008, Dominic Watson <[EMAIL PROTECTED]> wrote: > > You have to factor in the time as well, which makes this a range search > > and not an exact match. > > > Absolutely right, good way to be sure of that is to use DateDiff() as it > specifically compares the date unit you want which is days in this case: > > SELECT * > FROM myTable > WHERE DateDiff(d, aDateTimeCol, <cfqueryparam value="#startDateTime#" > cfsqltype="cf_sql_timestamp">) >= 0 > AND DateDiff(d, aDateTimeCol, <cfqueryparam value="#endDateTime#" > cfsqltype="cf_sql_timestamp">) <= 0 > > Don't have to fiddle with the dates that way. > > If you know the dates are just being set with the date info and no time > info, then BETWEEN is fine. Or if there is time data stored and you want to > use BETWEEN, you could do as Jeff says but use BETWEEN instead of the <= > and >=. > > Dominic > > -- > Blog it up: http://fusion.dominicwatson.co.uk > -- Blog it up: http://fusion.dominicwatson.co.uk ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298434 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
