copy timestamp to another field but convert to Y-m-d H:i:s first.

[jason310771]

OK I have tried various things and still can not get my head around it..

 

I am wanting to take the content of one field timestamp, time(), and convert it to a standard date format, yyyy-mm-dd 00:00:00  (Y-m-d H:i:s) and have this stored in another field where it current content is 0000-00-00 00:00:00

 

This query shows the correct results..

 

 

SELECT * FROM `table` WHERE `datetime` = '0000-00-00 00:00:00'

 

and this query does not return any errors, but does not change any of the fields. return zero changes.

 

UPDATE `table` SET `datetime` = date(`timestamp`) WHERE `datetime` = '0000-00-00 00:00:00'

 

The two fields are setup like so..

timestamp             is  int(11)

datetime               datetime default content 0000-00-00 00:00:00

 

 

 

what am I doing wrong to cause the fields not to correctly update ?

[mac_gyver]

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_from-unixtime

[jason310771]

great thank you