Query to convert for DATE in MySQL-CodePudding

Hello I have a question that transpose column type to DATE.

My Origint Data that for transpose to DATE is below. And there type is VARCHAR, structure is MM/DD/YY

how can i transpose to DATE type??

01-05-75
06-04-66
07-05-66
...

CodePudding user response：

You can use MySQL STR_TO_DATE function

SELECT 
    STR_TO_DATE(
        CONCAT(LEFT(`date`,6), '19', RIGHT(`date`, 2)), 
        '%m-%d-%Y'
    ) 
FROM 
    tab

Here's a fiddle.

CodePudding user response：

SELECT DATE(CONCAT(
    '19', SUBSTRING(col, 7, 2), /* year, assuming all years are between 1900 and 1999) */
    '-',
    SUBSTRING(col, 1, 2), /* month */
    '-',
    SUBSTRING(col, 4, 2) /* day */
))