This problem was previously reported at the bug database:
Bug#22646 'LC_TIME_NAMES: Assignment to non-UTF8 target fails'
http://bugs.mysql.com/bug.php?id=22646
It has been already fixed and the fix should appear in the next releases
of all active branches: 4.1, 5.0, 5.1.
This is what I get with after this fix applied:
mysql> CREATE TABLE `thetable` (
-> `thekey` varchar(16) collate ucs2_bin NOT NULL default '',
-> `thecolumn` varchar(8) collate ucs2_bin NOT NULL default '00000000',
-> PRIMARY KEY (`thekey`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_bin;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO `thetable` ( `thekey` , `thecolumn` ) VALUES ('1', DATE_FORMAT(NOW(), '%Y%m%d'));
Query OK, 1 row affected (0.03 sec)
mysql> select * from thetable;
+--------+-----------+
| thekey | thecolumn |
+--------+-----------+
| 1 | 20061201 |
+--------+-----------+
1 row in set (0.02 sec)