|
Posted by Thomas Bartkus on August 15, 2005, 5:04 pm
Please log in for more thread options
> 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
> realdate;
>
> 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.
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
manipulation.
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.
Then
I would either convert the field varchar->datetime
or
I would CONVERT them to datetime with a SELECT query and INSERT them into a
new table.
-Tom
| 
XML Sitemap