I am trying updates some dates in my system from mm/dd/yyyy to dd mmm yyyy but my script is not working - I am currently ad

update tng_events set deathdatetr = replace(date("yyyy-mm-dd",deathdate))
and set deathdate = replace(date("dd-mmm-yyyy",deathdatetr))
limit 0,2;

Any help would be great

Thanks Sam

I am not sure about it.this might work.try this

UPDATE tng_events SET `deathdatetr` = replace(`deathdatetr`, "mm/dd/yyyy", "dd/mmm/yyyy")

Welcome to WT samiam48,

Try (wrote on the fly and i know nothing about your database structure):

PHP Code:

<?php
//connect to database (see php.net/mysql_connect)
//select database (see php.net/mysql_select_db)
$query = mysql_query("SELECT id,deathdate FROM tng_events");
while($array = mysql_fetch_array($query)){
mysql_query("UPDATE tng_events SET deathdate ='".
date("d/m/Y", strtotime($array[1]))."' WHERE id = '".$array[0]."' LIMIT 1");
}
?>

yeah, it's better to make an extra column int the table and handle the conversion in PHP, after each conversion you could do an SQL UPDATE... a lot of them

What is the data type of deathdatetr?

It should be a TIMESTAMP or DATETIME, in which case the format in the DB doesn't matter, the datatype will have the date and time innately as a part of the object.

You should retrieve the value into a new DateTime() object, and use DateTime::format() to change the display format.

Yes, a unix timestamp is definitely much better or just datetime. But now that the database scheme already is created then I agree with jasonliu, do this in PHP. You will have much better control. mad_willsy already showed how you can do it.