Failed to update a datetime table value in Mysql in the '%d-%m-%Y %H:%i:%s' format

Question

I tried to update all table rows values using this sql function :

UPDATE night
SET started_at = DATE(
    STR_TO_DATE('13.05.2012 15:31:00','%d.%m.%Y %H:%i:%s')
)

but the result was 2012-05-13 00:00:00.

I'm using the SQLyog community edition v8.12 and I have the database hosted using the MySQL administrator v1.2.17 and I don't know if the sql syntax used on those software's has a specific version.

Answer 1

You are calling DATE() function:

DATE(expr)

Extracts the date part of the date or datetime expression expr.

Thus the time part of your expression is dropped. Just use:

UPDATE night
SET started_at = STR_TO_DATE('13.05.2012 15:31:00','%d.%m.%Y %H:%i:%s')

Answer 2

this worked for me :

UPDATE `night` SET started_at = (SELECT STR_TO_DATE('13.05.2012 15:31:00','%d.%m.%Y %H:%i:%s'));