tags:

views:

1929

answers:

6

I want to add 1 year to a datetime-type column in every single row in a table. Adding using an UPDATE statement is easy for numeric types. ex:

UPDATE TABLE SET NUMBERCOLUMN = NUMBERCOLUMN + 1

I'd like to do the same thing with a DATETIME-type...

UPDATE Procrastination SET DropDeadDueDate = DropDeadDueDate + ?

...but I'm not sure what value to use. Is there a numeric value I could use that means "1 year"? Or is there a DATEADD function or similar in SQL Server?

+6  A: 

There is in fact a DATEADD statement in T-SQL, you can find it here

UPDATE Procrastination SET DropDeadDueDate = DATEADD(yyyy,1,DropDeadDueDate)

EDIT: You could use year, yy, or yyyy for the first argument of DATEADD.

Matthew Jones
..and today0s lucky "fastest typist easy rep" competition is...
gbn
+1  A: 

UPDATE Procrastination SET DropDeadDueDate =
DATEADD(yy, 1, DropDeadDueDate)

ref: http://doc.ddart.net/mssql/sql70/da-db_5.htm

shahkalpesh
whilst the link is useful, wouldn't have a more recent MSDN link have been more helpful rather than a SQL7 one? The only reason I ask is that you criticised the OP for NOT researching the answer him/herself??
Kev Riley
@Kev:I am not criticizing :) Yes, the link is older but things haven't changed for that function, as far as I know. My idea is that questions/answers should be for things, one wouldn't find easily in documentation/MSDN. Hope that justifies my comments.
shahkalpesh
+1  A: 

The DateAdd function should do what you want.

UPDATE Procrastination SET DropDeadDueDate = DateAdd(yy, 1, DropDeadDueDate)
Vincent Ramdhanie
+1  A: 
UPDATE Procrastination SET DropDeadDueDate = DATEADD(year, 1, DropDeadDueDate)

http://msdn.microsoft.com/en-us/library/ms186819.aspx

Bob
@Bob: it should be DATEADD(year, 1, DropDeadDueDate)
shahkalpesh
Whoops, thanks, tried typing it out too fast, not fast enough though.
Bob
+3  A: 

SQL Server has a DATEADD function.

http://msdn.microsoft.com/en-us/library/aa258267(SQL.80).aspx

GuiSim
+4  A: 

It could be done with a DATEADD() function like this:

UPDATE Procrastination SET DropDeadDueDate = DATEADD(yy, 1, DropDeadDueDate)
CAbbott