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?

score 10 · Accepted Answer · answered Oct 01 '09 at 15:40

10

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

answered Oct 01 '09 at 15:40

KM.

101,727
34
178
212

1

the key is to floor by the unit you are interested in (in this case month), see this link: http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server/85379#85379 – KM. Oct 01 '09 at 15:49
What is the purpose of ORDER BY 2 ? – AnthonyWJones Oct 01 '09 at 15:51
1

it 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. – KM. Oct 01 '09 at 15:53

score 1 · Answer 2 · answered Oct 01 '09 at 15:40

1

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)

answered Oct 01 '09 at 15:40

LukeH

263,068
57
365
409

score 0 · Answer 3 · edited Oct 01 '09 at 15:35

0

There is a MONTH(GetDate()) T-SQL Function you can use.

Also are you just trying to strip the time from a date time?

If so, try this:

DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)

It's a bit cleaner and more readable.

edited Oct 01 '09 at 15:35

p.campbell

98,673
67
256
322

answered Oct 01 '09 at 15:34

JonH

32,732
12
87
145

Thanks 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. – AnthonyWJones Oct 01 '09 at 15:38

score 0 · Answer 4 · answered Oct 01 '09 at 15:40

0

may be this is more 'readable':

SELECT
 COUNT(1) AS [CountOfEvents],
 CONVERT(datetime, CONVERT(varchar, EventTime, 112)) + 1 - DAY(EventTime) AS [Month]
FROM [Event]
GROUP BY CONVERT(datetime, CONVERT(varchar, EventTime, 112)) + 1 - DAY(EventTime)

answered Oct 01 '09 at 15:40

manji

47,442
5
96
103

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

4 Answers4

Linked