CODEWITHSUNDEEP
mysqldate
Stanley Mungai
Stanley Mungai

Reputation: 4150

Convert String to date in Mysql

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

1990/02/22
22/02/1990
22-02-1990
02/22/1990

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?

Upvotes: 0

Views: 195

Answers (1)

Hardy
Hardy

Reputation: 1539

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:

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

Upvotes: 1

Related Questions