Question 113572 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search