Converting VARCHAR "date" info to an actual date field

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.

Any and all advice will be _greatly_ appreciated.

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

>(Sigh.) This means that the so-called date fields look like this :[color=blue]
>
>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;[/color]

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
example:
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.
[color=blue]
>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.[/color]

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

<usenet@isotopeREEMOOVEmedia.com> wrote in message
news:vsr1g150d7qpjept4cbsggq411pgc7op4l@4ax.com...[color=blue]
> I have inherited a table where date information was saved from PHP as a[/color]
VARCHAR.[color=blue]
> (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[/color]
field to[color=blue]
> 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[/color]
willing to[color=blue]
> work with complex field manipulations -- or will quite happily ALTER the[/color]
table[color=blue]
> to add a new realdate field to avoid future problems. But I'm pretty much[/color]
at a[color=blue]
> loss about where to start.[/color]

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

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

>>SELECT *, STR_TO_DATE(textdate,'%Y/%m/%d') as realdate FROM table ORDER BY[color=blue][color=green]
>>realdate;[/color]
>
>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
>example:
> 01 02 03 04 05 06
>now, which one is the year? the month?[/color]

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.