CODEWITHSUNDEEP
sqlsql-server-2008
MDIT
MDIT

Reputation: 1548

How do you update only the year in and keep day and month - sql server

I have a column that contain date and for each row i would like to update only the year for example :

-------------
date
-------------
22/01/2013
16/02/2013
19/08/2013
23/01/2013

i want to change for every row only the year part like this :

-------------
date
-------------
22/01/2012
16/02/2012
19/08/2012
23/01/2012

change it for the whole table thanks

Upvotes: 4

Views: 42091

Answers (3)

sukalyan
sukalyan

Reputation: 21

Update table_name set date_field=DateAdd(yyyy,2012-year(date_field),date_field)

Upvotes: 2

bab
bab

Reputation: 56

Using:

Update TableName set date = DateAdd(yy,-1,Date)

should subtract one year from each date field for you.

Upvotes: 16

JustinHui
JustinHui

Reputation: 729

Use DATEADD

UPDATE my_table
SET [date] = DATEADD(year,-1,[date])

http://sqltutorials.blogspot.co.uk/2007/06/sql-dateadd-function.html

Upvotes: 1

Related Questions