Convert string with different format to date

Question

I have a table with a string column (varchar(200)) that contain a date in different format. For example

may 24 1983 12:00AM
1981-01-13 00:00:00
1979-01-13 00:00:00:123

I want to convert this column in a date to extract year. How could i do? I have used STR_TO_DATE but don't work well. I must use SUBSTRING for each different format?

MySQL version 5.0.95

Bad schema. Bad. I would fix the data first (to a consistent convertible text value), then update the schema to use a real datetime column (and let the value be auto-converted or do whatever other magic is required for the conversion). Then use the standard data functions to extract the date. This problem only exists because the database schema is broken. — user166390
– user166390, Commented Oct 4, 2012 at 19:10
We are not discussing DB schema, which I agree with you it's really bad. The MAIN question is that I have this table as it is and i want to do this cleaning up job I must to convert these strings to datetime. How can you help? (With a constructive comment) — Luigi Saggese
– Luigi Saggese, Commented Oct 4, 2012 at 19:26

Alain Collins · Accepted Answer · 2012-10-04 20:03:33Z

5

There's a trick for detecting a valid date on the man page. You can use it to determine whether a STR_TO_DATE format worked.

select foo,
    case when length(date(str_to_date(foo,"%Y-%m-%d %H:%i:%S"))) is not null then str_to_date(foo,"%Y-%m-%d %H:%i:%S")
        when length(date(str_to_date(foo,"%b %d %Y %h:%i%p"))) is not null then str_to_date(foo,"%b %d %Y %h:%i%p")
    end as newdate
from my_table

Put one format for everyone you're expecting. Test like crazy.

Good luck.

(Oh, and congrats for trying to cleanup a bad schema!)

answered Oct 4, 2012 at 20:03

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luigi Saggese Over a year ago

Do you wanna know the further complication? Abbreviations months name are not in English! Do you advise me to do a clean up of abbreviations (eg Italian - English gen = jan)?

Alain Collins Over a year ago

If the abbreviations are in a bunch of different languages, then standardizing them will help. If they're in one (say, Italian), I would wonder if changing the default language in mysql would cover it for you.

Kermit · Accepted Answer · 2012-10-04 19:51:09Z

0

I would try to do this using a server side language, perhaps PHP and use strotime to generate a pretty date.

Otherwise, the only other option I see is to start writing CASEs to read possible formats, and output using DATE_FORMAT. Perhaps something like:

Pseudo-code (for case may 24 1983 12:00AM)

CASE
    WHEN col (after first space is some digit) 
        AND (after second space is 4 digits)
        AND (after third space has 'AM' or 'PM')
    THEN
        DATE_FORMAT(col, '%....')

You can also trying using REGEXP to for pattern matching.

answered Oct 4, 2012 at 19:51

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Collectives™ on Stack Overflow

Convert string with different format to date

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related