按日期的天分组

select dateadd(DAY,0, datediff(day,0, create_at)),count(0) 
    from one_on_one_message 
GROUP BY dateadd(DAY,0, datediff(day,0, create_at))
    order by COUNT(1) desc

参考

DATEADD (Transact-SQL) DATEDIFF (Transact-SQL)