Convert String to date in Mysql

Question

I am facing an issue where a date field was maintained as varchar in Mysql. Users have input dates in different formats e.g :

I need to convert these into one date format dd-mm-yyyy. Is there a clean way to do this? I am using phpmyadmin and when I try:

select STR_TO_DATE(cust_dob, 'dd-mm-yyyy') from my_table;

I am getting 00-00-0000 for all the fields. Anyone knows what I should do?

Do you have full access to the table? I would suggest taking the time to create a date field and run a series of queries to update the field using the varchar field as source. I don't think a function will ever be able to do what you want especially when you have something like 22/02/1990 and 02/22/1990. How would the function know what was intended? — Robbert
– Robbert, Commented Aug 30, 2013 at 16:40
@Robbert Not to mention that it is not possible to remove the ambiguity of 01/02/1990. Is it February or January? — Mosty Mostacho
– Mosty Mostacho, Commented Aug 30, 2013 at 16:42

Hardy · Accepted Answer · 2013-08-30 19:18:02Z

As your question, I guess you are about to maintain/retrieve data from an dataset which is not organise, not format or it is collect from many source. So the main problem you are facing is data normalisation.

I think you could do the following thing:

update your data to the same format X-Y-Z by replace '/' with '-' http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

mysql> SELECT REPLACE('31/12/2013', '/', '-');
        -> 'www.mysql'

re-arrange data to a unified form, such as 'dd-mm-yyyy'. this is the most complex since on your example data, there are no clue to know if they are format month/day or day/month. If you did not know it either, I think can make a guess (you can guess can base on data localisation, probability of most data or data itself like field with number > 12 definitely is day). By making guess, you are ready to accept error rate, update it lately when incorrect data found.
normalisation data by substring and join it back in order http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substring-index

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
    -> 'www.mysql' 
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
    -> 'mysql.com'

after everything done, simply use select

STR_TO_DATE(cust_dob, 'dd-mm-yyyy') from my_table;

Note: I guess a programming language like python can make thing become easier. give it a try.

Hope this can help and sorry about my bad English

Collectives™ on Stack Overflow

Convert String to date in Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related