Hi All,
In my last submission of Code Horror about the DateAdd Function.
i have come up with the solution with this Code snippet region.
Recap of the Issue: While Adding a Month in a Dates the next date should be the same date of the last month,
But the Dateadd() function will take you to wrong date if you passes over the Febrauary Month.
e.g
First Due Date: 31st Jan 2012
So for calculating Next Due Date we will use the DateAdd() function,
DATEADD(M,1,First_due_Date) -----> Result would be 29th Feb 2012
For next time you do process foe March it will be,
last_month_due_date: 29th Feb 2012
DATEADD(M,1,last_month_due_date) ----> 29th Mar 2012(Lag of two days from original)
Solution: In this kind of scenarios we need to keep track of our First Due Date with the Current due dates.
Here the sample code to get the correct Due date with respect to the First Due date.
ALTER FUNCTION [dbo].[Fun_Next_Due_Date](@FDD AS DATETIME,@NDD AS DATETIME,@intdue_increment AS INT) RETURNS DATETIME
AS
BEGIN
DECLARE @NDD_temp AS DATETIME
DECLARE @count AS INT
IF (DAY(@FDD) = 29 OR DAY(@FDD) = 30 OR DAY(@FDD) = 31) --Because We have issues in those 3 days in months
BEGIN
SET @NDD_temp = DATEADD(M,@intdue_increment,@NDD)
IF (DAY(@NDD_temp) != DAY(@FDD)) AND (DAY(@NDD_temp) < DAY(@FDD))
BEGIN
SET @count = DAY(@FDD) - DAY(@NDD_temp)
WHILE @count > 0
BEGIN
SET @NDD = @NDD_temp
SET @NDD_temp = DATEADD(d,1,@NDD_temp)
IF (ISDATE(@NDD_temp) = 1) AND (MONTH(@NDD_temp) = MONTH(@NDD))
BEGIN
SET @count = @count - 1
SET @NDD = @NDD_temp
END
ELSE
BEGIN
SET @count = 0
END
END
END
ELSE
BEGIN
SET @NDD = @NDD_temp
END
END
ELSE
BEGIN
SET @NDD_temp = DATEADD(M, @intdue_increment, @NDD)
SET @NDD = @NDD_temp
END
RETURN @NDD
END