How to cast DATETIME as a DATE in mysql?

Question

My query is this. I have a bunch of entries and i want to group them by date. But instead of having date in my database, I have a datetime field. What do I do?

select * from follow_queue group by follow_date cast follow_date as date

That's not working.

Try: `select * from follow_queue group by cast(follow_date as date)` first. — NawaMan, Sep 23 '09 at 22:16
Doesn't the DATE() function cast the datetime to a string? Sure, it works for grouping by date, but I think NawaMan's answer is more correct based on the phrasing of the question — DJDave, Feb 09 '16 at 13:54

score 198 · Accepted Answer · edited Oct 22 '16 at 01:43

198

Use DATE() function:

select * from follow_queue group by DATE(follow_date)

edited Oct 22 '16 at 01:43

rink.attendant.6

40,889
58
100
149

answered Sep 23 '09 at 22:15

ChssPly76

97,371
24
203
192

How do I get the "count" for entries per date? Just do select field,count(*) from follow_queue group by date(follow_date)? – Zack Burt Sep 23 '09 at 23:24
3

Yup. `select DATE(follow_date), count(*) from follow_queue group by 1` – ChssPly76 Sep 23 '09 at 23:31
2

Group by 1? What does that mean? – Zack Burt Sep 23 '09 at 23:45
12

It means group by first column in the select list, in this case `DATE(follow_date)`. You can do that with `ORDER BY` as well - `ORDER BY 1, 2`, for example – ChssPly76 Sep 23 '09 at 23:59

How to cast DATETIME as a DATE in mysql?

1 Answers1

Linked

Related