MySQL - Converting TEXT datatype (yyyy-mm) to DATE

Question

I have a column called "month" that I have imported into MySQL:

• Datatype: TEXT
• Display: 2021-01

My goal is to convert this column into some sort of DATE datatype (ie. DATE, DATETIME) so that MySQL will recognize it as yyyy-mm.

I have tried the following method but still received NULL

--- (1) add dummy date
SELECT CONCAT(month, '-01')
FROM tablename; 

--- (2) convert to DATE datatype 
SELECT CONVERT(month,DATE)
FROM tablename; -- if I just run (1) and (2) i receive NULL

--- (3) Format back to yyyy-mm format
SELECT FORMAT(month,'yyyy-mm') AS month2
FROM tablename; -- If i run from (1) to (3), i receive 2,021

So:

1. How do i solve this problem conversion problem in MySQL
2. Is there anyway i could have prevented this before/while importing?

Answer 1

If you are looking for a one time migration from your string format to a SQL date or datetime, then you can just do

UPDATE tablename SET columname = CONCAT(columname, '-01');

and then update the column to a DATE or a DATETIME.

Alternatively if you do not want to update the values you could use the following queries to cast to a DATE or a DATETIME:

SELECT CAST(CONCAT(columnname, '-01') AS DATE) FROM tablename

or

SELECT CAST(CONCAT(columnname, '-01') AS DATETIME) FROM tablename