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

Question

Post reply

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

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

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

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 · Answer 2

Declare @d DATETIME

Declare @E DATETIME

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

sreeramjithendra SSC Veteran Points: 216 More actions · Answer 3

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 · Answer 4

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