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.
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;