Clever Geek Handbook

T-SQL code for getting DateTime only contains month and year from any DateTime

Given a table Event

that contains a field of EventTime

type DateTime and that the value will contain date and time elements, I need to create a summary query that counts the number of events in each month.

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

This is what I have so far, but it is not very elegant and I am not sure if it is 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)))

Best deals for more efficiency or elegance?

+2


source to share


4 answers


by floors up to a month:

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

output:



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

(1 row(s) affected)

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
+10


source 


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)


+1


source


There is a MONTH(GetDate())

T-SQL function that you can use.

Also are you just trying to remove the time from the date?

If so, try this:

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

It's a little cleaner and more readable.

0


source


maybe 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)
0


source






More articles:


All Articles