3

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;
  • 1
    Do you need all the months of the year or all the days of the year? Your current setup looks like you would have 1-12 in the month column and 2012 in the year column. Better question would be - why do this at all? You can use DATEPART ( datepart , date ) to get the month and year of a given date. What problem are you trying to solve? – Thronk Dec 11 '15 at 3:22
  • @Thronk thanks, my question is how to add all days of the month, ie, each which days is part of the month, for example: I have this data, Month of the year = 1 and Year = 2012, I need fill table with all days which is part of the month (01/01/2012, 02/01/2012... , 31/01/2012) I not have idea how to realiza a query to solve this problem. – Fernando Bernal Dec 11 '15 at 18:37
  • You solved this with a cursor? I hope this is a once-only thing. – Rob Farley Dec 16 '15 at 21:12
  • @RobFarley, Thank's, I used a cursor as I need migrate the information between tables and I execute only one this transaction. – Fernando Bernal Dec 17 '15 at 18:40
4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.