> > i have a table where the dates are stored as a string like 5/12/03 -
> > stored in the date('U') format.
>
> Check out MySQL's DATE_FORMAT() function.
Unfortunately mySQL expects dates to be formatted YY[YY]MMDD in order to use
date functions and these dates are not stored that way. You are going to
have to use string functions to manipulate the data beofre you can convert
them to epoch seconds (what date('U') gives). I don't really see how
DATE_FORMAT would be helpful, UNIX_TIMESTAMP() will give you epoch seconds.
You might want to try something like this
UPDATE table_name SET col_name =
UNIX_TIMESTAMP(CONCAT(SUBSTRING(col_name,LENGTH(col_name)-1),LPAD(SUBSTRING_
INDEX(col_name,'/',1),2,'0'),LPAD(REPLACE(SUBSTRING_INDEX(SUBSTRING(col_name
,LOCATE('/',col_name)),'/',2),'/',''),2,'0')));
But I would try
SELECT
CONCAT(SUBSTRING(col_name,LENGTH(col_name)-1),LPAD(SUBSTRING_INDEX(col_name,
'/',1),2,'0'),LPAD(REPLACE(SUBSTRING_INDEX(SUBSTRING(col_name,LOCATE('/',col
_name)),'/',2),'/',''),2,'0')) AS Date FROM table_name;
first to make sure it is formatting everything correctly.
I didn't do a whole bunch of testing on the query, so use at your own risk,
back your table up first, and all that. Next time you can avoid all of this
madness by choosing a more portable format.
Here's the docs on UNIX_TIMESTAMP
http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1313
And here's the string functions page
http://www.mysql.com/doc/en/String_functions.html#IDX1164
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
