Converting VARCHAR "date" info to an actual date field

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have inherited a table where date information was saved from PHP as a VARCHAR.
(Sigh.)  This means that the so-called date fields look like this :

August 1, 2005, 9:09 am EDT
October 13, 2004, 12:28 pm EDT

This makes them essentially useless for sorting and logical evaluation.
Understandbly, the MySQL date functions don't seem able to convert the field to
a "real" date field.  This yields a blank (or maybe null) "realdate" :

SELECT *, STR_TO_DATE(textdate,'%Y/%m/%d') as realdate FROM table ORDER BY

Can anyone suggest a way to fix this (aside from data entry) ?  I'm willing to
work with complex field manipulations -- or will quite happily ALTER the table
to add a new realdate field to avoid future problems.  But I'm pretty much at a
loss about where to start.

Any and all advice will be _greatly_ appreciated.

Re: Converting VARCHAR "date" info to an actual date field

Quoted text here. Click to load it
field to
willing to
at a

You need to re-arrange the date string to the euro format "yyyy-m-d h:m"
that MySQL likes.
The best option would be to use any (of the many) languages adept at string
    However -
You can do it all using MySQL string functions in an update query. It's just
sort of ugly and complicated.

If you can front this database with Microsoft Access, you can make short
shrift of this problem using the VBA functions DATEVALUE() and TIMEVALUE()
which will interpret your given datetime string straight up (almost :-).
This is the cleanest option, but I can give you the complex MySQL only
solution if you ask.

What I might do is make one pass with an UPDATE query that rearranges the
dates/times strings.
I would either convert the field varchar->datetime
I would CONVERT them to datetime with a SELECT query and INSERT them into a
new table.


Re: Converting VARCHAR "date" info to an actual date field

Quoted text here. Click to load it

But the format you pass str_to_date doesn't match your examples!
You have to tell it what format to use.  To take a really ridiculous
    01 02 03 04 05 06
now, which one is the year?  the month?

Something like:
    str_to_date(textdate, '%M %d, %Y %h:%i %p')
might work.  I don't think it does timezones.  Read the documentation for
date_format to get the meaning of all the %codes.

Quoted text here. Click to load it

If you can get the format correct so it works, and you're going to be doing
much comparing and sorting on the field, I suggest making a new realdate
field, loading it from the text field:

  update table set realdate = str_to_date(textdate, '%M %d, %Y %h:%i %p');

modify your queries to use the new field (and, especially, enter a correct
date in realdate), and eventually drop the old one.

                    Gordon L. Burditt

Re: Converting VARCHAR "date" info to an actual date field

Quoted text here. Click to load it

Aargh!  I was (stupidly) specifying the output format, rather than the input
format.  Thanks for clueing me in, Gordon.  It's working now.

And thanks to Tom for the reply, too.

Site Timeline