获取DateTime的T-SQL代码仅包含来自任何DateTime的Month和Year [英] T-SQL code to get a DateTime contain only Month and Year from any DateTime
问题描述
给定一个表 Event
包含一个名为 EventTime
类型为DateTime的字段,该值将包含日期和时间元素,我需要创建一个汇总查询,计算每个月内的事件数。
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.
分组依据字段的结果类型也必须是日期时间0时间元素,并设置为每月的第一天。
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.
这是我迄今为止,但它不是很优雅,我不知道它特别有效。 p>
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?
推荐答案
这个楼层到这个月:
select dateadd(month,datediff(m,0,GETDATE()),0);
输出:
-----------------------
2009-10-01 00:00:00.000
(1 row(s) affected)
所以请尝试:
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
这篇关于获取DateTime的T-SQL代码仅包含来自任何DateTime的Month和Year的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!