I am updating Column2 with the year only from Column1. Since they both have DATE as data types, when I use the YEAR() or DATEPART() function they return an INT data type and therefore I get the error:
Operand type clash: int is incompatible with date
So I've tried converting it and I still get the same error
UPDATE [dbo].[TABLE]
SET [COLUMN2] = CONVERT(INT,DATEPART(YEAR,[COLUMN1]))
If anyone could point me in the right direction on how to fix this.
Thanks.
if it is a DATE data type, you can't just store an integer year like 2015, 2016 in it
Either you change the data type to INT or perhaps store the Jan 1 date in it
SET [COLUMN2] = DATEADD ( YEAR , DATEDIFF ( YEAR , 0, [COLUMN1] ) , 0 )
If you are looking to update Column2 with the year from Column1 but preserve the Month and Day in Column2 then you could use the following:
UPDATE Table
SET Column2 = CONCAT(DatePart(yy,Column1),'-',DatePart(mm,Column2),'-',Datepart(dd,Column2))
I.e Column1 has 2015-04-04 , Column2 has 2011-01-01 but you want column2 to take the year from column1 to represent 2015-01-01.
Hopefully I have understood this correctly 
Beware that it is much faster to use integer arithmetic (only) for such date manipulation, rather than date-to-string and string-back-to-date parsing
Also I suggest avoiding using '-' or other punctuation formatting in the date as it makes it ambiguous. "yyyy-mm-dd" is fine as a format when parsed to a DATE datatype, but not to a DATETIME datatype (in the latter the parsing depends on a host of conditions, which might change, including Server Locale and the Language of the current session's user etc. etc.)
In both cases the string format "yyyymmdd" (no punctuation) is always treated as unambiguous and we always use that, even for DATE datatype, in case one day we forget and accidentally use "yyyy-mm-dd" when converting to an old-style DATETIME datatype 
From SQL 2012 onwards
DATEFROMPARTS ( year, month, day )
is available to construct a date if the year, month and day are available as numbers
I haven't done any performance tests, but it might be that:
SET Column2 = DATEFROMPARTS(YEAR(Column1),MONTH(Column2), DAY(Column2))
would be as efficient as the more obtuse DATEADD/DATEDIFF that KHTan posted (and which used to be the common, performance-efficient, way to "round" date/time). DATEFROMPARTS is also a lot easier to read!
Personally I avoid the abbreviated codes for DATEPART and similar functions. "DATEPART(mm" is confusable between Month, Minute, Millisecond or Microsecond. The abbreviations "n", "w" and "dy" are all valid but will another developers reliably know what they mean? They are not the obvious choices of Nanosecond, Week and Day .... I find the full datepart labels "year", "month", etc. much more readable, and no chance of another developer wrongly assuming what they mean because they are unambiguous.
I don't get an error but also no rows update. And yes, there is data in column 1. Its just not updating.
DECLARE @intRowCount int
UPDATE ...
SELECT @intRowCount = @@ROWCOUNT
SELECT [UPDATE Row Count] = @intRowCount
do you really see a ZERO for the Update Row Count?
EDIT: Fixed typo COUNT(*) -> @@ROWCOUNT
I'm using the code from khtan
SET [COLUMN2] = DATEADD ( YEAR , DATEDIFF ( YEAR , 0, [COLUMN1] ) , 0 )
Yeah, but it is inconceivable (given the, perhaps simplified, code you posted showing your UPDATE statement) that it is not updating any rows.
UPDATE MyTable
SET MyColumn = SomeValue
WILL update EVERY row in the table. No IFs, BUTs or MAYBEs ...
Hence why I suggested that you add code to see how many rows are actually updated (using your ACTUAL update statement). That then tells us whether it is actually not updating (e.g. you have WHERE clause that is preventing update), or whether it is but something else is in play - such as a TRIGGER that is undoing the change, or the new data is being updated and is actually exactly the same as the old data ... or some other factor.