How to add one year to a date using SQL script in SQL server?

I am getting a date parameter value as '4-1-2009' from front end. Now I want to make it as

'4-1-2010' in my stored procedure. I am trying like below.

ALTER PROCEDURE [dbo].[SP_EMP]                    

 @STARTDATE DATET开发者_StackOverflow社区IME,

 @ENDDATE DATETIME,

 @STARTDATE2 DATETIME, 

 SET  @STARTDATE2=DATEADD(yy, 1, @STARTDATE)


AS                      
BEGIN 

SELECT EMPNAME FROM EMP WHERE JOINDATE>@STARTDATE2

----//  SOME JOINS //----

END

How can I do this? Please tell me.

Regards, N.SRIRAM

dateAdd function id the solution

SELECT  DATEADD(year, 1, '4-1-2009') FROM  UserLog

Or

Declare @E DATETIME,

SET @E=Select DATEADD(year, 1, '4-1-2009')

select dateadd(yy, 1, '20 Jan 2011')

I had the same issue as the person asking the question. The other answers are not addressing his issue.

What he has is variables and needs to change the date in them by incrementing. You were on the right track.

Here is a demonstration you can copy and paste into SSMS and it will just work.

    /*First declare your varabiles, you can use date or datetime, or even var only after using dateadd the format will change */
Declare @CTCStartDate date
Declare @CTCEndDate date

/* Now define your initial values, you may want to have these by a SSRS report or other program */
    Set @CTCStartDate = '2015-01-01'
    Set @CTCEndDate = '2015-11-11'

    /* See the inital values */
    Select @CTCStartDate as InitialStartDT, @CTCEndDate as InitialEndDT

    /* Increment the year by the number you desire, even this can be a variable */
    Set @CTCEndDate = DATEADD(YYYY,1, @CTCEndDate)
    Set @CTCStartDate = DATEADD(YYYY,1, @CTCStartDate)

    /* See the final results */
    Select @CTCStartDate as StartDT, @CTCEndDate as EndDT

Imagine you want to increase all articles you have in your website by one year, if you want to only change it when displaying:

SELECT DATEADD(year, 1, CreationDateTime) AS CreationDateTime
FROM [dbo].[Articles]

You can place '2009/04/01' instead of CreationDateTime. If you also want to change the value in database:

UPDATE [dbo].[Articles] SET CreationDateTime = DATEADD(year, 1, CreationDateTime)

FYI if you want to decrease on year it goes like this: DATEADD(year, -1, CreationDateTime)

Read more here