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

  • Hi All,

    I am getting a date parameter value as '4-1-2009' (for @D parameter in SQL server)

    from front end. Now I want to make it as

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

    Declare @D DATETIME,

    Declare @E DATETIME,

    SET @E=?

    I want '4-1-2010' for @E. How can I do this?

    Please tell me.

    Regards,

    N.SRIRAM

  • select DATEADD(yy,1,getdate())

    this statement will add 1 year to getdate().

    yy indicates you are adding years, 1 is the amount of years.

    for your SP and script...use this sort of method:

    declare @d datetime

    set @d = '4-1-2009'

    select DATEADD(yy,1,@d)

    results:

    2010-04-01 00:00:00.000

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Declare @D DATETIME

    Declare @E DATETIME

    SET @E= DATEADD(Year, 1, @D)

  • Thanks for reply. But it is giving error. I am trying like below.

    ALTER PROCEDURE [dbo].[SP_EMP]

    @STARTDATE DATETIME,

    @ENDDATE DATETIME,

    @STARTDATE2 DATETIME,

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

    AS

    BEGIN

    SELECT EMPNAME FROM EMP WHERE JOINDATE>@STARTDATE2

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

    END

    Regards,

    n.sriram

  • you must DECLARE variables, and no comma's after each.

    try it as:

    ALTER PROCEDURE [dbo].[SP_EMP]

    DECLARE @STARTDATE DATETIME

    DECLARE @ENDDATE DATETIME

    DECLARE @STARTDATE2 DATETIME

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

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply