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

[Jagadish Talluri]

Hi Peter, PFB, the mentioned issue

----------------------------------------------------------------------------------
mysql> SHOW VARIABLES LIKE '%CHARAC%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | ucs2 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>
mysql> SHOW VARIABLES LIKE '%COLLAT%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | ucs2_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)

mysql> show create table char_set_testx;
+----------------+------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+----------------+------------------------------------------------------------------------------------------------------------------
| char_set_testx | CREATE TABLE `char_set_testx` (
`id` int(11) DEFAULT NULL,
`my_date` varchar(64) DEFAULT NULL,
`my_date_new` varchar(64) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 |
+----------------+------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> select * from char_set_testx;
+------+---------------------+---------------------+
| id | my_date | my_date_new |
+------+---------------------+---------------------+
| 1 | 02/14/2013 14:28:30 | 02/14/2013 14:28:30 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select id,str_to_date(my_date,'%m/%d/%Y %H:%i:%s'),str_to_date(my_date_new,'%m/%d/%Y %H:%i:%s') from char_set_testx;
+------+------------------------------------------+----------------------------------------------+
| id | str_to_date(my_date,'%m/%d/%Y %H:%i:%s') | str_to_date(my_date_new,'%m/%d/%Y %H:%i:%s') |
+------+------------------------------------------+----------------------------------------------+
| 1 | NULL | 2013-02-14 14:28:30 |
+------+------------------------------------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '' for function str_to_date |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%CHARAC%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | ucs2 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>
mysql> SHOW VARIABLES LIKE '%COLLAT%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | ucs2_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> select id,str_to_date(my_date,'%m/%d/%Y %H:%i:%s'),str_to_date(my_date_new,'%m/%d/%Y %H:%i:%s') from char_set_testx;
+------+------------------------------------------+----------------------------------------------+
| id | str_to_date(my_date,'%m/%d/%Y %H:%i:%s') | str_to_date(my_date_new,'%m/%d/%Y %H:%i:%s') |
+------+------------------------------------------+----------------------------------------------+
| 1 | NULL | 2013-02-14 14:28:30 |
+------+------------------------------------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '' for function str_to_date |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
---------------------------------------------------------------------------------

Kindly help.



Edited 1 time(s). Last edit at 03/04/2013 07:52AM by Jagadish Talluri.