Re: Is STR_TO_DATE function doesn't work with UCS2 charset?

[Rick James]

Your queries demonstrate that a VARCHAR in latin1 or ucs2 can be correctly converted to utf8 or latin1 in the client.

And it demonstrates that the server-side function STR_TO_DATE() works with latin1, but not with ucs2.

Try this:
SELECT str_to_date(CONVERT(my_date USING latin1),'%m/%d/%Y %H:%i:%s') from char_set_testx;
That should convert your ucs2-encoded bytes to latin1-encoded bytes, at which point, str_to_date() will be happier.
Those steps happen in the server, before the parameters in SHOW VARIABLES LIKE 'char%' get involved.

http://dev.mysql.com/doc/refman/5.1/en/charset-convert.html
http://mysql.rjweb.org/doc.php/charcoll