如何在SQL Server中按月计算两个日期之间的天数？

I have Two Date in SQL sever which overlap in two month i want to find how many days over lap in each month. For example:

我在SQL服务器中有两个日期,它在两个月内重叠我想知道每个月有多少天。例如:

Start date is : 26-Sep-2012
End Date is : 10-Oct-2012

Sept- 5 days
October - 10 days

along with the list of date of each month.

以及每个月的日期列表。

3 个解决方案

#1

declare @start datetime;
declare @end datetime;
set @start = '20120926';
set @end = '20121010';

SELECT (DAY(DATEADD (m, 1, DATEADD(d, 1 - DAY(@start), @start))-1)
        - DAY(@start) + 1) AS DaysLeftStart,
        DAY(@end) AS DaysEnd

Fiddle: http://sqlfiddle.com/#!3/d41d8/4441/0

#2

DECLARE @start DATETIME, @end DATETIME;

SELECT @start = '20120926', @end = '20121010';

;WITH c(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @start, @end)+1)
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, @start)
    FROM sys.all_columns
) 
SELECT 
  [date] = DATEADD(MONTH, DATEDIFF(MONTH, 0, d), 0), 
  [days] = COUNT(*)
FROM c GROUP BY DATEDIFF(MONTH, 0, d)
UNION ALL SELECT d, NULL FROM c;

Results:

date                        days
-----------------------     -----
2012-09-01 00:00:00.000     5
2012-10-01 00:00:00.000     10
2012-09-26 00:00:00.000     NULL
2012-09-27 00:00:00.000     NULL
2012-09-28 00:00:00.000     NULL
2012-09-29 00:00:00.000     NULL
2012-09-30 00:00:00.000     NULL
2012-10-01 00:00:00.000     NULL
2012-10-02 00:00:00.000     NULL
2012-10-03 00:00:00.000     NULL
2012-10-04 00:00:00.000     NULL
2012-10-05 00:00:00.000     NULL
2012-10-06 00:00:00.000     NULL
2012-10-07 00:00:00.000     NULL
2012-10-08 00:00:00.000     NULL
2012-10-09 00:00:00.000     NULL
2012-10-10 00:00:00.000     NULL

#3

-1

Here you can find some detail about creating a calendar table. You can use it to perform such query:

在这里,您可以找到有关创建日历表的一些详细信息。您可以使用它来执行此类查询:

SELECT CalendarMonth, count(*) day_num 
FROM dbo.Calendar 
WHERE CalendarDate  between @start_date and @end_date;

#1

declare @start datetime;
declare @end datetime;
set @start = '20120926';
set @end = '20121010';

SELECT (DAY(DATEADD (m, 1, DATEADD(d, 1 - DAY(@start), @start))-1)
        - DAY(@start) + 1) AS DaysLeftStart,
        DAY(@end) AS DaysEnd