T-SQL code to get a DateTime contain only Month and Year from any DateTime

Question

Given a table Event containing a field called EventTime of type DateTime and that the value will contain both date and time elements, I need create a summary query which counts the number of events in each month.

The resulting type of the Group By field must also be Date Time with a 0 time element and set to 1st day of the month.

This is what I have so far but its not very elegant and I'm not sure its particularly efficient.

  SELECT COUNT(1) AS [CountOfEvents],
         DATEADD(d, 1 - DAY(EventTime), DATEADD(dd, 0, DATEDIFF(dd, 0, EventTime))) AS [Month]
    FROM [Event]
GROUP BY DATEADD(d, 1 - DAY(EventTime), DATEADD(dd, 0, DATEDIFF(dd, 0, EventTime)))

Better suggestions for either more efficiency or elegance?

Answer 1

this floors to the month:

select dateadd(month,datediff(m,0,GETDATE()),0);

output:

-----------------------
2009-10-01 00:00:00.000

(1 row(s) affected)

so try this:

SELECT
   COUNT(*) as CountOF
    ,dateadd(month,datediff(m,0,EventTime),0)
    FROM [Event]
    GROUP BY dateadd(month,datediff(m,0,EventTime),0)
    ORDER BY 2