I am working on TableA that has Column1 with dates in this format: YYY-MM-DD and Column2 with numbers range from 1-12. I am trying to change the date year (THE YEAR ONLY) to 2022 WHERE Column2 = 10

NOTE: I am not trying to change the months and date.

UPDATE TableA
SET Column1 = '2021'
WHERE Column2 = 10

CodePudding user response：

If you are storing your dates as date data type, then you could do it mathematically as the following:

For SQL server:

update TableA
set Column1 = dateadd(year, -(year(Column1) - 2022), Column1)
where Column2 = 10

Demo

For MySQL

update TableA
set Column1 = date_add(Column1, interval -(year(Column1) - 2022) year)
where Column2 = 10

Demo

CodePudding user response：

Ideally you would never store a date as a string - use the correct datatype i.e. date. Also I assume YYY-MM-DD is a typo and that it should read YYYY-MM-DD.

Then a simple string concatenation should work

create table TestTable (Column1 varchar(10), Column2 int);

insert into TestTable (Column1, Column2)
select '2021-12-10', 10;

update TestTable set
    Column1 = CONCAT('2022', SUBSTRING(Column1,5,6))
where Column2 = 10;

select * from TestTable;

Returns:

Note: Works on both SQL Server and MySQL.

CodePudding user response：

UPDATE TableA
SET Column1 = '2022'   substring(Column1,6,5)
WHERE Column2 = 10

So it is hard replacing 2022 to the first 4 characters and appending the rest of column1 to the end