Very Computer

I have a table with

datetime EntryDate = getdate()
int EventID

I want to see how many times each event occurs on each day.

the best i can come up with is:

SELECT
COUNT(*),
DATEPART(yy, EventDate) AS yy,
DATEPART(mm, EventDate) AS mm,
DATEPART(dd, EventDate) AS dd
FROM [Events]
GROUP BY yy, mm, dd
ORDER BY yy desc, mm desc, dd desc

which extracts the yy/mm/dd components and then groups by them.
I have also used the convert function to create a string of :
yy + '/' + mm + '/' + dd and then convert that back into a datetime.

now functionally i have no problems whatsoever. works just great.
i was just curious as to whether this is the most efficient way to do
this or not.

i'm sure it would be more efficient to have a datefield which just contained
0,0,0 for hours minutes and seconds but i want to retain the hours,
minutes and seconds for other reasons. i'd rather not have 2 date columns
for different purposes.

The above does seem quite fast even with thousands of rows, but it
just looked suspicious to me so i thought i'd seek expert advice.

any thoughts?

thanks,
simon

SELECT CONVERT(VARCHAR, dateCol, 101),
       COUNT(*)
  FROM tbl
 GROUP BY CONVERT(VARCHAR, dateCol, 101)

--
- Anith
(Please respond only to newsgroups)

Regards
Habib

| You can do:
|
| SELECT CONVERT(VARCHAR, dateCol, 101),
|        COUNT(*)
|   FROM tbl
|  GROUP BY CONVERT(VARCHAR, dateCol, 101)
|
| --
| - Anith
| (Please respond only to newsgroups)
|
|
|
|