CODEWITHSUNDEEP
mysqlstr-to-date
ansh
ansh

Reputation: 623

Convert different string date, into one single date format In mysql

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 enter image description here

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.

Upvotes: 0

Views: 1112

Answers (2)

Taher Mahodia
Taher Mahodia

Reputation: 314

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

https://www.guru99.com/regular-expressions.html

Upvotes: 2

lorenzo-s
lorenzo-s

Reputation: 17010

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')

Upvotes: 1

Related Questions