Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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...

like image 493
Joshua Carmody Avatar asked Jun 04 '09 17:06

Joshua Carmody


People also ask

How can I update the year from a date in SQL?

To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows. Display all records from the table using select statement.

How can I update a datetime field in SQL?

To update a date field with T-SQL, here is the general syntax: UPDATE table_name SET date_field = 'date_value' [WHERE conditions]; To update with the current date: UPDATE table_name SET date_field = getdate();


2 Answers

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.

like image 181
Matthew Jones Avatar answered Oct 03 '22 23:10

Matthew Jones


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

UPDATE Procrastination SET DropDeadDueDate = DATEADD(yy, 1, DropDeadDueDate) 
like image 42
CAbbott Avatar answered Oct 03 '22 21:10

CAbbott