 Posted by Manicse under Sql Server category on | Points: 40 | Views : 1603
Hi All,

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 DATETIMEASBEGINDECLARE @NDD_temp AS DATETIMEDECLARE @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 @NDDEND`