Search code examples
mysqlsqlsqlyogmysqladministrator

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

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.

Solution

  • 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')