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

datetime, sql, sql-server, t-sql

Solution

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

Problem

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?

Original source

Related problems