MySQL Forums
Forum List  »  Newbie

Re: Is STR_TO_DATE function doesn't work with UCS2 charset?
Posted by: Jagadish Talluri
Date: March 04, 2013 07:50AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.