Sql query to Group by day

Question

I want to list all sales, and group the sum by day.

Sales (saleID INT, amount INT, created DATETIME)

Update I am using SQL Server 2005

Answer 1

If you are using the SQL Server, dateadd(DAY,0, datediff(day,0, created)) then it will return the created day.

For example, if the sale is created on '2009-11-02 06:12:55.000', dateadd(DAY,0, datediff(day,0, created)) then it'll return '2009-11-02 00:00:00.000'

Try this:

select sum(amount) as total, dateadd(DAY,0, datediff(day,0, created)) as created

from sales

group by dateadd(DAY,0, datediff(day,0, created))