I would use the DATEDIFF() function: SELECT DATE(orderdate) AS thedate, SUM(ordertotal) AS ordersubtotal FROM tblOrders WHERE DATEDIFF(current_date(), orderdate) <= 30 GROUP BY DATE(orderdate)
(I think this should work; I use MSSQL, so can't test this...) Seb Seb Duggan Web & ColdFusion Developer e: s...@sebduggan.com t: 07786 333184 w: http://sebduggan.com On 28 Dec 2008, at 22:32, Will Tomlinson wrote: > I have an orders table with a datetime field. I'm trying to query > orders that are x number of days back from today. Ex. Show me all > orders 30 days back from now. > > SELECT date(orderdate) as thedate, sum(ordertotal) AS ordersubtotal > FROM tblOrders > where orderdate >= current_date() - 30 > group by date(orderdate) > > The query works, but it's returning orders all the way back to 2006 > - Not correct! > > What's weird is, if I change the value from 30, to 7, It shows the > correct orders. I can change it all the way up to 28, and it's > correct. If you change it to 29, which runs into last month, it > pulls EVERYthing. Very strange! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:317190 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
