T-SQL code to get a DateTime contain only Month and Year from any DateTime
Solution 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
Solution 2
SELECT
COUNT(1) AS [CountOfEvents],
DATEADD(month, DATEDIFF(month, 0, [EventTime]), 0) AS [Month]
FROM [Event]
GROUP BY DATEADD(month, DATEDIFF(month, 0, [EventTime]), 0)
AnthonyWJones
General interests: C#, Javascript, Agile, Interaction Design Current interests: Silverlight+Toolkit, Windows Phone 7. Would like to know more about: WCF, EF, WPF, XNA, SQL 2008, ... the list is endless. First to gain the Silverlight badges, now at Gold.
Updated on June 14, 2022Comments
-
AnthonyWJones almost 2 years
Given a table
Event
containing a field calledEventTime
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?
-
AnthonyWJones over 14 yearsThanks I am striping the Time and that is an element in my expression (look closely you can see it) however I don't want to strip the Year I just want to set the day to the 1st. So the MONTH function isn't all that helpful.
-
KM. over 14 yearsthe key is to floor by the unit you are interested in (in this case month), see this link: stackoverflow.com/questions/85373/floor-a-date-in-sql-server/…
-
AnthonyWJones over 14 yearsWhat is the purpose of ORDER BY 2 ?
-
KM. over 14 yearsit will sort the query by the second column. When I ran this query on my system, it was difficult to check the results, so I sorted them. When I pasted it in here, I just left that there. remove if you don't need it.