SQL Server : setting date using DATEPART

Question

I have a table with some rows that have had their date year set to 1990. I want to set all these rows to 2016. So for example, 1990-01-06 will become 2016-01-06.

This is what I have up to now

UPDATE [tableName]
SET recievedDate = new DateTime() 
     //where I want to make a new datetime with 2016 as the year
WHERE DATEPART(yyyy, receivedDate) = '1990' 
  AND DATEPART(mm, receivedDate) = '01';

Answer 1

You could use DATEADD to add 26 years to 1990:

CREATE TABLE #tableName(receivedDate DATE);
INSERT INTO #tableName(receivedDate) VALUES ('1990-01-06'),('1990-01-16');

UPDATE #tableName
SET receivedDate = DATEADD(YEAR, 26, receivedDate)
WHERE DATEPART(yyyy, receivedDate) = '1990' 
  AND DATEPART(mm, receivedDate) = '01';

SELECT *
FROM #tableName;

LiveDemo

---

SQL Server 2012+ you could use DATEFROMPARTS:

UPDATE #tableName
SET receivedDate = DATEFROMPARTS(2016,
                                 DATEPART(mm, receivedDate),
                                 DATEPART(dd, receivedDate)
                                )
WHERE DATEPART(yyyy, receivedDate) = '1990' 
  AND DATEPART(mm, receivedDate) = '01';

LiveDemo2

EDIT

Keep in that expression DATEPART(yyyy, receivedDate) = '1990' is not SARGable and query optimizer will skip index ob receivedDate(if exists any). You could use BETWEEN or >= < like Gordon Linoff proposed.

Answer 2

Since CONVERT(varchar, yourdate, 126) returns a string in folwoing format "2016-01-04T07:45:00" one can just use following statement:

update YourTable set 
    yourdate = CAST('2016' + substring(CONVERT(varchar, yourdate, 126), 5, 15) as datetime)
where YEAR(yourdate) = 1990 AND MONTH(yordate) = 1

Answer 3

The right solution is to add 26 years as lad2025 explains. However, the query itself is better written like this:

UPDATE tableName
    SET receivedDate = DATEADD(YEAR, 26, receivedDate)
    WHERE receivedDate >= '1990-01-01' AND
          receivedDate < '1990-02-01';

This version allows the query to take advantage of any index that includes receivedDate as the first column, which can be a significant improvement in performance.