PHPology is a collective of highly skilled, award winning, web gurus.

MYSQL: Updating the date value of a datetime field

Whilst working on a very old project relating to Twitter, the dates that the tweets were created was 2 years old and was not appearing on this site. The tweets would appear based on the current date/time.

Wanting to update the 'created' column, I only wanted to update the date part of the datetime value so that I was able to set the date for today just to get some data back. 

Below is the example I used which worked a treat. 

I used the below query to give me an idea of what the new datetime value wil be:

SELECT *, concat('2015-07-10 ', time(created)) as datetime FROM `twitter` WHERE date( created ) = '2013-06-10'

 

Once I was happy with the results, I used the below query to update the values. 

 

UPDATE twitter SET created = concat('2015-07-10 ', time(created)) WHERE date(created) = '2013-06-10'