I have an column create_date in my sql table, datatype is varchar(225). My problem is, in that column values are in different date formats Please check the attached Image
I want to create another column with constant_date_format using create _date column, is there way?
I have tried with mysql str_to_date function:
STR_TO_DATE(create_date, '%m/%d/%Y')
it is working if create_date column have same date format.
You can case when with regular expression
SELECT CASE WHEN create_date REGEXP [0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] THEN STR_TO_DATE(create_date, '%m-%d-%Y') WHEN create_date REGEXP Format THEN STR_TO_DATE(create_date, '%m/%d/%Y') ELSE create_date END AS create_date FROM TABLE_NAME
If your data is like the one in the example, it's impossible to do that. Consider if you have a row in create_date with value 8.1.2017: what's constant_date_format for that? It's 1/8/2017 or 8/1/2017?
You have, at least, define a fixed day/month order in create_date. Then you can use REPLACE on create_date to make sure you always use slahses or dots, and finally STR_TO_DATE to convert to date. For example, if you decide that create_date have month before day, you can do:
STR_TO_DATE(REPLACE(create_date, '.', '/'), '%m/%d/%Y')
