True, if at least one side of the date comparison is a constant then it is better to structure the comparison in such a way that functions can be avoided.
On Tue, Dec 22, 2009 at 3:56 PM, Leigh <[email protected]> wrote: > > > SET @dateVal_2 = '2009-12-22 23:59:59.000' > > You will see that the result is quite different. If you only > > want to know two date > > values are on the same day regardless of time, then using > > dateDiff() may be safer. > > If the comparison is structured correctly, the results are the same (or > marginally better if the database considers milliseconds). A comparison > that disregards time would compare against the #startDate# (at midnight) and > #dayAFTERTheDesiredEndDate# (at midnight). > > For example, the query below would find all records dated any _time_ on > 12/22. The same concept could be applied to any range. > > WHERE DateColumn >= '2009-12-22' > AND DateColumn < '2009-12-23' > > Also, functions may discourage optimizers from properly utilizing available > indexes. Resulting in less efficient queries. So it is often better to avoid > functions. Assuming the same results can (reasonably) be achieved without > them. > > -Leigh > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329332 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
