Server:如何添加每个月的所有日期？

Question

我需要帮助。我想把这个月的所有日期加起来。我有这样的信息：

Month | Year
=============
  1   | 2012
  2   | 2012
  3   | 2012
  4   | 2012

我需要添加月份的所有日期(例如: 01/01/2012至31/01/2012)。

我不知道怎么加所有的日期。拜托，任何帮助都是非常有用的。

编辑的

我使用游标解决了我的问题，游标运行表"MontsOfYear“中的记录，并且每个寄存器获得月份的最后一天(例如:月份=1和年份=2015年)，从而获得1月(31)的最后一天，接下来是在第一天到月份的最后一天之间迭代。

这是代码：

DECLARE db_Cursor CURSOR FOR SELECT Years,Months FROM MontsOfYear;
DECLARE @Years INT, @Months INT, @LasDate date, @DatePart date, @Flag int;

OPEN db_Cursor;

FETCH NEXT FROM db_Cursor INTO @Ano, @Mes
WHILE @@fetch_status = 0
BEGIN

SET @LasDate = (SELECT (DATEADD(MONTH, DATEDIFF(MONTH, 0,       DATEFROMPARTS(@Years,@Months,1)) + 1, 0) - 1));
SET @DatePart = DATEFROMPARTS(@Ano,@Mes,1);
SET @Flag = 1;

    WHILE @DatePart < @LasDate
        BEGIN
            SET @DatePart = (DATEFROMPARTS(@Ano,@Mes,@Flag));
            INSERT INTO Days VALUES(@Ano,@Mes,@DatePart);
            SET @Flag = @Flag + 1;

        END;
FETCH NEXT FROM db_cursor INTO @Ano, @Mes;
END;
CLOSE db_Cursor;
DEALLOCATE db_Cursor;

Answer 1

使用像这样的函数，从我的回答到使用函数获取两个日期之间的日期列表。：

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

...and像这样查询它：

SELECT *
FROM YourTable t
CROSS APPLY dbo.ExplodeDates(
    DATEFROMPARTS(t.[Year], t.[Month], 1), 
    EOMONTH(DATEFROMPARTS(t.[Year], t.[Month], 1))
) AS d;