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

Ness_ness_sh 0
Viewed 27

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.

mysql-workbench mysql database sql
2 Answers

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);
Meyssam Toluie0

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

usermx530
Related