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,
DECLARE @Temp DATETIME
SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
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).
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
yields 2020-01-01, which is the start date, not 1 month later.
Any ideas? Anyone?