SQL stored procedure: increment months from a starting date to an end date

Question

I'm trying to create a stored procedure in SQL Server that basically increments the months from a given start date up to a given end date and updates them into a predefined table.

Problem: Somewhat like this:

Exec MonthRunner @Start ='2014-01-01', @End = '2014-06-01'

Should give me a table like this:

Date 
2014-01-01
2014-02-01
2015-03-01
2015-04-01
2015-05-01
2015-06-01

Approach: A loop programmed using a cursor. Something similar to this:

Create Procedure MonthRunner(@Start date, @End date)
AS
DECLARE @Date date
DECLARE @getid CURSOR


SET @getid = CURSOR FOR
Select (@Start)


Set @getid 
OPEN @getid 
FETCH NEXT
FROM @getid into @Date


WHILE @@FETCH_STATUS = 0
BEGIN
SET @Date = (Select Dateadd(Month, 1, @Date))
DECLARE @sqlrun varchar(max)

SET @sqlrun= 'Update myTable' + 'Set Date=' + @Date
EXEC @sqlrun


fetch next 
from @getid into @Date
END

CLOSE @getid
DEALLOCATE @getid

So far my result is:

Update myTable Set Date='2014-02-01'

But there is no loop and also I don't know how to terminate the loop using the variable @End.

Your help would be greatly appreciated! Thanks in advance,

Clemens

Please look here: http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function — Evaldas Buinauskas, Jun 18 '15 at 14:20
Darn, I was so focused on my cursor that I forgot how to loop. — user4985694, Jun 18 '15 at 14:20
Just go for the CTE solution provided by user Alivia. It's probably the best one. On top of that, avoid cursors as much as you can. — Evaldas Buinauskas, Jun 18 '15 at 14:23

score 0 · Accepted Answer · answered Jun 18 '15 at 14:19

This can easily be done with a recursive CTE:

;WITH cte AS (
    SELECT      @Start      AS [Month]
    UNION ALL
    SELECT      DATEADD(MONTH, 1, [Month])
    FROM        cte
    WHERE       [Month] < @End
)

SELECT  [Month]
FROM    cte
OPTION  (MAXRECURSION 0)

score 0 · Answer 2 · answered Jun 18 '15 at 14:25

0

I looped similar to this:

DECLARE @Start date
DECLARE @End date 
DECLARE @counter date

set @counter = @Start
    while @counter <= @End
    begin
          print 'The counter is ' + cast(@counter as char)
         set @counter = (Select Dateadd(Month, 1, @counter))
    end

what do you think about this solution? (of course I have to change the text)

answered Jun 18 '15 at 14:25

user4985694

13
1
4

Why would you want to loop in SQL? It's extremely inefficient... I get the desire for a loop effect but SQL is all about dealing with sets so you want to stay away from imperative code like this. – Kittoes0124 Jun 18 '15 at 14:26

score 0 · Answer 3 · answered Sep 28 '17 at 13:16

increment months from a starting date to an end date in sp

ALTER PROCEDURE "dbo"."monthIncrementSp"
AS
    DECLARE @startDate VARCHAR(50);
    DECLARE @endDate VARCHAR(50);

    SET @startDate = '01-01-2017';
    SET @endDate = '31-12-2017';

    SELECT TOP (DATEDIFF(MONTH, CONVERT(DATE, @startDate, 105),CONVERT(DATE, @endDate, 105))+1)
    DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY name)-1, CONVERT(DATE, @startDate, 105))
    FROM sys.all_columns

Kittoes0124 · Answer 4 · 2015-06-18T14:37:01.843

Here's a table-valued function to get the beginning of a month:

CREATE FUNCTION dbo.MonthBegin (   
    @date DATE = NULL
  , @offset INT = 0
)
RETURNS TABLE   
AS   
RETURN (
    SELECT D = DATEADD(m, DATEDIFF(m, 0, @date) + @offset, 0)
);

Alone, this function doesn't quite do what you need. Combine it with a set of integers though and you can have a lot of fun:

DECLARE @seedDate DATE = GETDATE();

SELECT *
FROM dbo.RangeInt(-100, 100) Nums
CROSS APPLY dbo.MonthBegin(@seedDate, Nums.N) Dates;

The above example uses a TVF to generate a set of numbers between -100 and 100 and then passes those numbers to the MonthBegin TVF (along with a seed date). You could also write this to be based off of a numbers table or CTE... whatever is most familiar/comfortable to you.

SQL stored procedure: increment months from a starting date to an end date

4 Answers4