How to extract year from a DATE column and insert into new column in MySQL-CodePudding

I have a column "date" with data type date and format "YYYY-MM-DD".

I would like to create a new column having only the year "YYYY".

I tried YEAR() and EXTRACT() functions but to my understanding those are queries and I cannot insert them into a column later on.

Any thoughts on that?(please keep in mind that I am novice at best)

CodePudding user response：

First you need to update your table's schema with an ALTER TABLE statement as follows:

ALTER TABLE <your_tab_name> ADD COLUMN `year` INT;

Then you can use an UPDATE statement and your function YEAR to update your newly created field with the extracted year value.

UPDATE <your_tab_name> 
SET `year` = YEAR(`date`)