Search code examples
mysqldatetimecastingtype-conversionmysql-workbench

Covert varchar(150) to datetime in sql workbench

I have a column with values '2015-02-14 12:23 AM' declared as varchar(150), I tried using to_date, convert and cast but not able to change the format. I would need this to filter on specific month/year/day. Thanks for the help

PS: Mysql instance is running on RDS through amazon AWS - not sure if its relevant

Solution

  • I suggest storing dates on proper date data type.

    to_date is an Oracle function.

    In MySQL, you can use STR_TO_DATE to converts a string to date then use Date_format to give the format you need

    SELECT DATE_FORMAT(STR_TO_DATE(dt,'%Y-%m-%d %h:%i %p'),'%m/%Y/%d') 
from test;

    Demo

    You can use substring with concat without the need of converting to date format:

    select concat(substring(dt,6,2),'/', substring(dt,1,4),'/',substring(dt,9,2)) as my_date
from test;

    Demo