MySQL str_to_date problem

What's wrong with this? str_to_date('26/04/2011 00:00:00', '%d/%m/%Y') It gives Error Code: 1292 Truncated incorrect date value: '26/04/2011 00:00:00'

Update: The problem is the 00:00:00, if I remove it it works. How can edit开发者_开发知识库 the '%d/%m/%Y' to accept the time? '%d/%m/%Y %h:%m:%s' doesn't work.

str_to_date('26/04/2011 00:00:00', '%d/%m/%Y %H:%i:%s')

Note the capital %H for hour (00-24) instead of %h (01-12).

Since you've specified the time in the value parameter, you should also specify the time components in the date format parameter.

str_to_date('26/04/2011 00:00:00', '%d/%m/%Y %h:%i:%s')

either that, or drop the time component from your date value:

str_to_date('26/04/2011', '%d/%m/%Y')

either should work, but you need to be consistent between the two parameters.

Alternatively, you could specify the format so that it has fixed values in the time component:

str_to_date('26/04/2011 00:00:00', '%d/%m/%Y 00:00:00')

this will work, but only if the time component is always 00:00:00.