How to cast varchar to int or datetime in MySQL

To convert a varchar value into int:

update TEMP set PATIENT_ID=cast(PATIENT_ID_ORIG as UNSIGNED);

To convert a varchar value into datetime

One need to use a troublesome function STR_TO_DATE. The problem with this function is that it never produces errors, instead it produces NULL and a warning that has to be revealed by an additional statement:

SELECT STR_TO_DATE('05/11/2012 08:30:00','%d/%m/%Y %H:%i:%s');
2012-11-05 08:30:00

SELECT STR_TO_DATE('505/11/2012 08:30:00','%d/%m/%Y %H:%i:%s');
null

SHOW WARNINGS;
Warning 1411 Incorrect datetime value: '505/11/2012 08:30:00' for function str_to_date

There might also be crazy conversion to zero dates. I converted valid values like that:

SET  sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES';
SELECT @@SESSION.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

update TEMP set ENTREE_SALLE =STR_TO_DATE(ENTREE_SALLE_ORIG,'%d/%m/%Y %H:%i:%s');