SQL Group By DateTime (MS SQL Server 2000) [Resolved]

[señorbadger]

Hello, i am trying to write an SQL query to group by the date an item was published on a daily basis ie: desired result

Name Count Date

mark 1 30/10/2006
bob 3 1/11/2006
chris 1 1/11/2006

so on and so on, i just need the group by to ignore the time portion of the datetime.

Below is my SQL snippet (SQL Server 2000 by the way)

I read somewhere by converting the date to a varchar it would ignore the date portion but the query below...

Declare @start DateTime
Declare @end   DateTime
Declare @days  int

set @start = convert(datetime, '14/11/2006', 103)
set @days = 6
set @end = DATEADD(d, @days, @start)



select Convert(char(11), document.published_Date) as PublishDate, count(distinct  document.document_series_id) as NumPublished, contact_summary.fullname
  from document inner join contact_summary on 
contact_summary.contact_id = document.author_id 
where document.published_Date > @start and document.published_date < @end 
group by Convert(char(11), document.published_Date), contact_summary.fullname 
order by Convert(char(11), document.published_Date) asc, count(distinct  document.document_series_id) desc

if i write a query to get the count of the documents published within the date span
the values dont match (sum of all days) != count of documents published on all days

By the way ALL documents have a valid author_id

Thank you!

Sam Lad