CODEWITHSUNDEEP
mysqlsqldatetype-conversionmariadb
Petr Fořt Fru-Fru
Petr Fořt Fru-Fru

Reputation: 1006

Convert VARCHAR data to DATE in MariaDB v. 10.1.xx

I have a table where the 'birth date' column is a VARCHAR data type, I need this column and try to convert it to DATE. I found the function "STR_TO_DATE" in the MariaDB documentation that I tried.

For example: Table

===================
| ID | Birth_Date |
===================
| 1  | 10.02.1990 |
-------------------
| 2  | 2.5.1986   |
===================

I'm Trying:

UPDATE   Table
SET      Birth_Date = STR_TO_DATE(Birth_Date, '% d/ %m /% Y');

But the SQL server returns:

\ Incorrect datetime value: '10.02.1990' for function str_to_date.

Is there a simpler procedure in MariaDB?

Upvotes: 0

Views: 1728

Answers (1)

Mureinik
Mureinik

Reputation: 311938

The values in your table use . as a delimiter. You should use it in the format string too:

STR_TO_DATE(Birth_Date, '%d.%m.%Y');

Upvotes: 3

Related Questions