Certainly true, Ian. Once I get the "day" part working correctly, I'll add the "month" and "year" to it, as well.
I thought I'd go for the most simple approach first, then make it more complicated. It's still not returning proper values in the different queries, however. But at least I'm not getting CF or MySQL errors! (see the post I just sent for the problem...) Rick -----Original Message----- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: Friday, November 05, 2010 5:57 PM To: cf-talk Subject: Re: I think I'm just making this way too hard... On 11/5/2010 2:44 PM, Rick Faircloth wrote: > Anyway, here's what finally worked: > > select totalNew > from 04a_dailyNumberNewHMLSProperties > where day(date_add(dateOfTotal, interval -1 day)) = > #dateFormat(dateAdd('d', -1, now()), 'd')# I'm glad you got something to work! But you may want to reconsider your choice of function. A quick internet search showed that the MySQL 5 day() function is to: "Returns the day of the month for /|date|/, in the range |1| to |31|" Thus day('2010-11-05') will return 5 as will the associated dateFormat() function. But since you do not include month or year portion you are going to return results of every dateOfTotal record that is the 5th day of the month for every month for every year in your database. I imagine that is not the desired result. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:338876 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
