How can I convert a varchar into a date in MySQL Workbench?-CodePudding

Probably it's super simple but i've been stuck some hours on this. I have a column called "Publish_Date" which is a varchar, but my date shows like this: 17.01.11 (year.day.month) and I want to convert it to a date (at this point, any date format it's ok).

Every time i tried to use "convert" or "cast" it gives me a syntax error or the data doesn't change or all the data in the column changes to "null" values. I'd appreciate if you can help me.

CodePudding user response：

Assuming your data is all greater than 2000 then you can add missing part of YEAR then cast it.

SELECT CAST(CONCAT('20', Publish_Date) AS DATETIME);

CodePudding user response：

You can use STR_TO_DATE with the format %y.%m.%d since this is how your date value is stored

select 
 
  str_to_date(birth_date, '%y.%m.%d')
from 
   mytable

Here is an SQL Fiddle I created for this case