MySQL change column data type with converting rule-CodePudding

I have a column with int, That only included year. I want to change its data type to DATETIME, with converting(for example, 2022 will be converted to 2022-01-01). Is any way to converting it without losing data?

CodePudding user response：

Follow the pseudo
convert numeric to string using CAST function
example. select cast ($year as CHAR) from tab
Further use STR_TO_DATE to convert the string to datetime 
example . select str_to_date(cast($year as CHAR), format )from table

CodePudding user response：

If all the current column values are just years, and you want to change the actual stored type to datetime, you would do:

update foo set bar=bar*10000 101;
alter table foo change bar bar datetime;

fiddle