CODEWITHSUNDEEP
sqlsql-server
Joshua Carmody
Joshua Carmody

Reputation: 13730

How can I use a SQL UPDATE statement to add 1 year to a DATETIME column?

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?

ADDITIONAL QUESTION

I would like to do this for not one field, but for every field in the database of data type 'datetime'. Is there an easy way to select all fields of type 'datetime' and perform an update of adding x amount of years? I am new to sql so please be gentle...

Upvotes: 31

Views: 112382

Answers (6)

GuiSim
GuiSim

Reputation: 7559

SQL Server has a DATEADD function.

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

Upvotes: 5

Matthew Jones
Matthew Jones

Reputation: 26190

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.

Upvotes: 67

CAbbott
CAbbott

Reputation: 8098

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

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

Upvotes: 9

Bob
Bob

Reputation: 99774

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

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

Upvotes: 5

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

The DateAdd function should do what you want.

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

Upvotes: 4

shahkalpesh
shahkalpesh

Reputation: 33474

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

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

Upvotes: 1

Related Questions