Recently i have got a error in my application.
My application is based on banking. While payment happened in every month we need to move the next payment date to the next month.

Consider a scenario,

Payment Date is : 20th Aug

So if he pays for August then the Next payment date will be 20th Sep using the function in sql.

• DATEADD(M,1,date)

Now if a user is paying for the month of January, his current next date is

Payment Date: 30th Jan

Now we are adding the DateADD function.

DATEADD(M,1,'01/30/2012')  -----> Output will be 29th FEB(for Leap Year) and 28th FEB(Non Leap Years)

So all the dates will be wrong with the further Dateadd() function.

Mani.R

You can check whether the entered year is leap year or not, when you are adding the month to January.
But i think that its the property of SQL Server, it will automatically check that the year entered is leap year or not.

G. S.
.Net Developer

Try to keep the initial payment date and increase the month interval. like DateAdd(M,interval(go like 1,2,3...every month),Initial Due Date).

my simple suggestion to this question is instead of adding months to the next date you simply add 30 / 31 / 29 / 28 according to month.

-- Code To find the Financial Year
Declare @FinYear nVarchar(20)
Declare @Date Datetime

--Set @Date = '2012-01-29 00:00:00.000'
Set @Date = '2012-03-29'

select DATEADD(d,30,@Date )
this will work.

Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in