I have a function that schedules future due dates at intervals based on a specific start date. I had help with this right here in this forum many (10?) years ago, but I admit, I never quite got the modulus logic involved. In any case, the intervals have changed, and now my function is not working properly.

Here is the function:

CREATE FUNCTION [dbo].[fnDueDate]

(

@DOP AS DATETIME,

@INTERVAL AS TINYINT,

@ThisDate DATETIME

)

RETURNS DATETIME

BEGIN

DECLARE @Temp DATETIME

SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)

RETURN @Temp

END

Basically this function, given a start date (@DOP), a month interval(@Interval), and an effective date (usually getdate()) should output the next scheduled date based on the monthly interval. Basically it gives you a date @interval months from the start date based on the effective (current) date (@thisdate).

SELECT dbo.fnDueDate

(

'2020-01-01',

3,

'2020-01-26'

)

yields

**2020-04-25** (@interval months from the start date)

This is correct!

However, the due date interval is now only one month, not three, and the function fails with a 1 in the interval parameter, so

SELECT dbo.fnDueDate

(

'2020-01-01',

1,

'2020-01-26'

)

yields **2020-01-01**, which is the start date, not 1 month later.

Any ideas? Anyone?