CODEWITHSUNDEEP
mysqlsqldatabase
YangDongJae
YangDongJae

Reputation: 105

Query to convert for DATE in MySQL

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
...

Upvotes: 0

Views: 37

Answers (2)

lemon
lemon

Reputation: 15482

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.

Upvotes: 1

obe
obe

Reputation: 7808

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 */
))

Upvotes: 1

Related Questions