CODEWITHSUNDEEP
mysqldatetype-conversionvarchar
MDTeach
MDTeach

Reputation: 45

MYSQL varchar to date

I'm having some trouble when converting one column to date.

I want this '01/02/98'(day, month, year) to convert to '1998-02-01'(year, month, day).

And also how you convert '98' to 1998.

Upvotes: 0

Views: 66

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

I want this '01/02/98'(day, month, year) to convert to '1998-02-01'(year, month, day). And also how you convert '98' to 1998.

STR_TO_DATE() handles both cases.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

The formats are explained here https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

%e matches day of the month, numeric (0..31)
%m matches month, numeric (00..12)
%y matches year, numeric (two digits)

Query

select str_to_date('01/02/98', '%e/%m/%y');

Result

| str_to_date('01/02/98', '%e/%m/%y') |
| ----------------------------------- |
| 1998-02-01                          |

demo

Upvotes: 1

Related Questions