set a date column in sql based on a another column-CodePudding

So I have a SQL Table with two columns:

Col1      Col2
202205    NULL
202204    NULL
202103    NULL

Now I want to set the Date in Col2 based on the value of Col1. So the result should look like this:

Col1    Col2
202205  2022-05-01
202204  2022-04-01
202103  2021-03-01

CodePudding user response：

Assuming that Col1 be text, we can try using the TO_DATE function here:

WITH yourTable AS (
    SELECT '202205' AS Col1
)

SELECT TO_DATE(Col1 || '01', 'YYYYMMDD') AS Col2  -- 2022-05-01
FROM yourTable;

CodePudding user response：

MySQL:

UPDATE  test_tbl
SET Col2 = STR_TO_DATE(CONCAT(Col1,'','01') ,'%Y%m%d');