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

• sreeramjithendra

  SSC Veteran

  Points: 216

  More actions

  January 19, 2011 at 11:45 pm

  #230665

  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

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  January 19, 2011 at 11:49 pm

  #1275691

  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

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  January 20, 2011 at 12:02 am

  #1275697

  Declare @D DATETIME

  Declare @E DATETIME

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

• sreeramjithendra

  SSC Veteran

  Points: 216

  More actions

  January 20, 2011 at 12:08 am

  #1275700

  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

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  January 20, 2011 at 12:26 am

  #1275713

  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