CODEWITHSUNDEEP
mysqlsql
mohan111
mohan111

Reputation: 8865

How to convert MM/YY to YYYY-MM-DD in MYSQL

I have got sample data in below format (MM/YY).

Dt
11/13
4/85
4/84
5/14
09/06

I need to convert that data ito YYYY-MM-DD

       Dt
    2013-11-01
    1985-04-01
    1984-04-01
    2014-05-01
    2006-09-01

I have tried using Date_Format(Dt,%y%m%d) but still it is giving error .

I know this format in the file is not correct but I have got the Data like I need to do a turn around using MYSQL . Any suggestions on this

Upvotes: 0

Views: 152

Answers (2)

Nick
Nick

Reputation: 147176

You need to use STR_TO_DATE to convert the text into a date:

SELECT STR_TO_DATE(CONCAT('01/', Dt), '%d/%m/%y') AS Dt
FROM data

Note that it's necessary to add a day to the string to avoid issues if NO_ZERO_DATE or NO_ZERO_IN_DATE SQL modes are enabled.

Output:

Dt
2013-11-01
1985-04-01
1984-04-01
2014-05-01
2006-09-01

Demo on SQLFiddle

Upvotes: 3

Henry Harutyunyan
Henry Harutyunyan

Reputation: 2415

You can do this 

SELECT DATE_FORMAT(STR_TO_DATE('12/12', "%m/%y"), '%Y-%m-01');

STR_TO_DATE("12/12", "%m/%y") will convert the string to a date format. DATE_FORMAT functino will convert the date to str making the day 01. You can convert it back to DATE if needed.

Upvotes: 2

Related Questions