CODEWITHSUNDEEP
mysql
jeffkolez
jeffkolez

Reputation: 648

How do I do a 'group by' for a datetime when I want to group by just the date?

That's a really awkward title :)

I need to write a report that generates a list of declines by date. The date is store as a datetime field and is returning the number of declines by datetime.

Here's my SQL:

select ps.date_scheduled, count(*) from payments_schedule ps
left join invoices i on i.invoice_id=ps.invoice_id
left join orders o on o.order_id=i.order_id
where ps.date_scheduled>'2009-06-01 00:00:00'
group by ps.date_scheduled

2009-06-25 14:13:04 1 2009-06-25 14:13:07 1 ..etc...

Not cool. What I want is this: 2009-06-25 25 2009-06-26 31 etc...

How do I do that? Thanks :)

Upvotes: 2

Views: 340

Answers (3)

Adamski
Adamski

Reputation: 54715

Change your query to select DATEADD(day, 0, DATEDIFF(day, 0, ps.date_scheduled)). This will preserve only the date portion of the date-time. You will also need to group by the same expression.

EDIT: This is how to solve the problem for SQL Server.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425713

SELECT  CAST(ps.date_scheduled AS DATE), COUNT(*)
FROM    payments_schedule ps
LEFT JOIN
        invoices i
ON      i.invoice_id = ps.invoice_id
LEFT JOIN
        orders o
ON      o.order_id = i.order_id
WHERE   ps.date_scheduled > '2009-06-01 00:00:00'
GROUP BY
        CAST(ps.date_scheduled AS DATE)

Upvotes: 7

Alex Barrett
Alex Barrett

Reputation: 16475

GROUP BY DATE(ps.date_scheduled)

Upvotes: 4

Related Questions