MySQL datetime filed: Truncated incorrect datetime value

Question

I am trying to convert string to mysql datetime format. using below format. but tis giving Truncated incorrect datetime value message.

following this page format https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

How to fix this function?

update userdata set    validity_start_date=STR_TO_DATE('10/29/2015 2:12 PM','%m/%d/%Y %h:%i');
ERROR 1292 (22007): Truncated incorrect datetime value: '10/29/2015 2:12 PM'

Thanks

Answer 1

You have "PM" in your string without giving it a format code for it.

mysql> select STR_TO_DATE('10/29/2015 2:12 PM','%m/%d/%Y %h:%i') as d;
+---------------------+
| d                   |
+---------------------+
| 2015-10-29 02:12:00 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '10/29/2015 2:12 PM' |
+---------+------+----------------------------------------------------------+

Use %p for the "AM"/"PM" part:

mysql> select STR_TO_DATE('10/29/2015 2:12 PM','%m/%d/%Y %h:%i %p');
+-------------------------------------------------------+
| STR_TO_DATE('10/29/2015 2:12 PM','%m/%d/%Y %h:%i %p') |
+-------------------------------------------------------+
| 2015-10-29 14:12:00                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

(No warning when you do this.)