如何使用sql从日期字段按月分组 [英] How to group by month from Date field using sql
本文介绍了如何使用sql从日期字段按月分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何从日期字段中仅按月分组(而不是按天分组)?
How can I group only by month from a date field (and not group by day)?
这是我的日期字段的样子:
Here is what my date field looks like:
2012-05-01
这是我当前的 SQL:
Here is my current SQL:
select Closing_Date, Category, COUNT(Status)TotalCount from MyTable
where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31'
and Defect_Status1 is not null
group by Closing_Date, Category
推荐答案
我会用这个:
SELECT Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0),
Category,
COUNT(Status) TotalCount
FROM MyTable
WHERE Closing_Date >= '2012-02-01'
AND Closing_Date <= '2012-12-31'
AND Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;
这将按每个月的第一天分组,所以
This will group by the first of every month, so
`DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130128'), 0)`
将给出 '20130101'
.我通常更喜欢这种方法,因为它将日期作为日期.
will give '20130101'
. I generally prefer this method as it keeps dates as dates.
或者你可以使用这样的东西:
Alternatively you could use something like this:
SELECT Closing_Year = DATEPART(YEAR, Closing_Date),
Closing_Month = DATEPART(MONTH, Closing_Date),
Category,
COUNT(Status) TotalCount
FROM MyTable
WHERE Closing_Date >= '2012-02-01'
AND Closing_Date <= '2012-12-31'
AND Defect_Status1 IS NOT NULL
GROUP BY DATEPART(YEAR, Closing_Date), DATEPART(MONTH, Closing_Date), Category;
这真的取决于你想要的输出是什么.(在您的示例中不需要关闭年份,但如果日期范围跨越年份边界,则可能需要).
It really depends what your desired output is. (Closing Year is not necessary in your example, but if the date range crosses a year boundary it may be).
这篇关于如何使用sql从日期字段按月分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文