date - str_to_date driving me nuts in mysql -

update `smart_userstest` set lastlogin = str_to_date(lastlogin,'%d/%m/%y %h:%i') lastlogin regexp('^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$') 

have query have used before... except getting errors.

so have imported excel spreadsheets hitting db. lots of date fields. in new format trying fix. m/d/yyyy hour:min month, day, hour, , min can 1-2 numbers. rid of hour:min portion , set date proper mysql date. account hour:min portion being left blank because have both.

example error: 1411 - incorrect datetime value: '1/24/2014' function str_to_date

you can solve case:

update `smart_userstest`     set lastlogin = (case when lastlogin '%/%/% %:%' str_to_date(lastlogin,'%d/%m/%y %h:%i')                           when lastlogin '%/%/%' str_to_date(lastlogin,'%d/%m/%y')                      end)     lastlogin regexp('^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$'); 

to me, strange setting character column date.

a better approach load data staging table, smart_userstest_staging. columns varchar(). there should no errors loading in.

then create smart_userstest logic on each field convert right value or check values correct (often, copy them in). gives ability find bad data in original table , helps fixing problems.

modifying data in place prevents having audit trail of had working. means next time have task, have relearn on again. if load staging table, logic encapsulated in sql query transfers data staging table real table.

edit:

try this:

update `smart_userstest`     set lastlogin = str_to_date(substring_index(lastlogin, ' ', 1),'%d/%m/%y')     lastlogin regexp('^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$'); 

this chooses m/d/yyyy portion of string. way, don't see how string hour/minute passing regexp pattern. think problem might somewhere else.