Group by Date

[shepherd]

I need to create a rollup table, with the data grouped by day. The field that I will be grouping by is a standard datetime field which records the exact time a transaction is made. Is there an efficient way to do this? When I run a cast or convert to int on the date field, it rounds up (so a date entered as July 4, 1996 4:00pm rounds up to July 5 - definitely not what I want). I want to avoid having to convert the data to a char type just to do this - there's got to be a better way. Does anyone have any suggestions?

 

[fluteplr]

I am pretty sure converting to a character type is it.

as in.

select convert(char(10), mydate,102) mydate, count(*)
from mytable
group by convert(char(10), added,102)