SQL Server 查询日期范围之间一个月的总天数 [英] SQL Server query for total number of days for a month between date ranges
问题描述
我有特定的日期范围,例如
I have specific date range like
From Date To Date
---------------------------
2012-11-10 2012-11-15
2012-11-21 2012-11-22
2012-11-30 2012-12-01
我想写一个 SQL 查询来计算两个日期之间的总天数和特定月份的总天数
I want to write a SQL query which calculates the total no of days between two dates and sum total number of days of particular month
我想要的输出是,
No of days month
--------------------
9 11
1 12
谁能帮我写这个 SQL 查询?
Can anyone help me to write this SQL query?
推荐答案
理想情况下,您有一个名为Dates"的表,其中包含您将使用的所有日期,例如1950 年到 2100 年.这个查询会给你你想要的结果:
Ideally, you have a table named "Dates" with all the dates you will ever use, e.g. year 1950 through 2100. This query will give you the result you want:
select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
from dates d
join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;
结果:
| themonth | COLUMN_1 |
-------------------------
| 2012-11-01 | 9 |
| 2012-12-01 | 1 |
请注意,与其仅将11"或12"显示为月份,如果您的范围超过 12 个月,这将无法正常工作,或者在跨过新年时无助于排序,此查询改为显示该月的第一天.
如果没有,您可以根据下面的扩展查询虚拟创建一个 dates
表:
If not, you can virtually create a dates
table on the fly, per the expanded query below:
;with dates(thedate) as (
select dateadd(yy,years.number,0)+days.number
from master..spt_values years
join master..spt_values days
on days.type='p' and days.number < datepart(dy,dateadd(yy,years.number+1,0)-1)
where years.type='p' and years.number between 100 and 150
-- note: 100-150 creates dates in the year range 2000-2050
-- adjust as required
)
select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
from dates d
join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;
此处提供了完整的工作示例:SQL Fiddle
The full working sample is given here: SQL Fiddle
这篇关于SQL Server 查询日期范围之间一个月的总天数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!