SQL query to group by day

if you're using SQL Server,

dateadd(DAY,0, datediff(day,0, created)) will return the day created

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

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

For SQL Server:

GROUP BY datepart(year,datefield),     datepart(month,datefield),     datepart(day,datefield)

or faster (from Q8-Coder):

GROUP BY dateadd(DAY,0, datediff(day,0, created))

For MySQL:

GROUP BY year(datefield), month(datefield), day(datefield)

or better (from Jon Bright):

GROUP BY date(datefield)

For Oracle:

GROUP BY to_char(datefield, 'yyyy-mm-dd')

or faster (from IronGoofy):

GROUP BY trunc(created);

For Informix (by Jonathan Leffler):

GROUP BY date_columnGROUP BY EXTEND(datetime_column, YEAR TO DAY)

If you're using MySQL:

SELECT    DATE(created) AS saledate,    SUM(amount)FROM    SalesGROUP BY    saledate

If you're using MS SQL 2008:

SELECT    CAST(created AS date) AS saledate,    SUM(amount)FROM    SalesGROUP BY    CAST(created AS date)