group by datetime

here is problem with datetime data type
when taking
group by createdate
that is in 8/8/2007 12:00:00 AM format
I dont want group for time 12:00:00 AM only group by for
date 8/8/2007 which format is mm/dd/yyyy
how can I doSELECT DATEADD(dd,DATEDIFF(dd,0,createdate),0) as thedate
, COUNT(*) as rows
FROM ...
GROUP BY DATEADD(dd,DATEDIFF(dd,0,createdate),0)|||Or, if u want to get it as mm/dd/yyy

select count(*), convert(varchar(10),createdate,101) from Table1 group by convert(varchar(10),createdate,101)|||good news on this front with the latest CTP for sql server 2008. WE ARE FINALLY GOING TO HAVE SEPERATE DATA TYPES FOR THE DATE AND THE TIME. it's about friggin' time. this was always one the most annoying product features. i heard they tried to slip it into 2K5 but they ran out of time.|||... but they ran out of time.badump-bump tsshhhhh ;)|||good news on this front with the latest CTP for sql server 2008. WE ARE FINALLY GOING TO HAVE SEPERATE DATA TYPES FOR THE DATE AND THE TIME. it's about friggin' time. this was always one the most annoying product features. i heard they tried to slip it into 2K5 but they ran out of time.
Ya know, this is an issue that has never really caused any problem for me, though I certainly hear enough other people whining about it.|||Thanks to you all I got my answer :)