> I have a column 'tmp_date' that was imported from a messy data set, trying
> to clean it up. The date is in the format of:
> m/d/yy and mm/dd/yy
>
> Somehow, I need to update a timestamp(14) column based on the tmp_date as
> the input.
Nice challenge ;-)
For MySQL 4.0.x:
SET @d='01/02/03';
SELECT DATE_FORMAT(
CONCAT_WS(
'-',
SUBSTRING(@d, LOCATE( '/', @d, LOCATE( '/', @d)+1)+1) ,
SUBSTRING_INDEX(@d, '/',1),
SUBSTRING(SUBSTRING_INDEX(@d,'/',2), LOCATE('/', SUBSTRING_INDEX(@d,
'/', 2))+1)
),
'%Y-%m-%d %H:%i:%s');
(Works for both formats you specified; replace @d with column name)
For MySQL 4.1.1+:
SET @d='01/02/03';
SELECT STR_TO_DATE(@d, '%m/%d/%Y');
(not tested)
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
