Sql-server – SQL Server: How to add all dates of the month

datetimesql serversql server 2014

I need help. I am trying to add all dates for the month. I have this information:

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

I need to add all dates of the month (for example: 01/01/2012 to 31/01/2012).

I do not have any idea how to add all dates. Please, any help is very useful.

Edited

I resolved my problem using a cursor, the cursor runs the records in the table "MontsOfYear" and with each register obtains the last day of the month (Example: Month = 1 and Year = 2015) that obtain the last day to January (31), next is iterate between the first day to the last day of the month.

This is the code:

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;

Best Answer

Use a function like this one, from my answer to Get a list of dates between two dates using a function:

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 query it like:

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