Bug #3143 Can't insert datetime as string if character set is utf8

• Submitted: 11 Mar 2004 8:57
• Modified: 11 Mar 2004 16:01
• Reporter: [ name withheld ]
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1a
• OS: Windows (Win2K)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[11 Mar 2004 8:57] [ name withheld ]

Description:
The following command produces a zero datetime in the database:

set character set utf8;
insert into mytable (mydatetime) VALUES ('20040312013912');

however, these all work:

set character set utf8;
insert into mytable (mydatetime) VALUES (20040312013912);

set character set sjis;
insert into mytable (mydatetime) VALUES ('20040312013912')

set character set sjis;
insert into mytable (mydatetime) VALUES (20040312013912)

The column is defined as:

+-------------+--------------+------+-----+---------------------+---------------
-+
| Field       | Type         | Null | Key | Default             | Extra
 |
+-------------+--------------+------+-----+---------------------+---------------
-+
| mydatetime  | datetime     |      |     | 0000-00-00 00:00:00 |
 |
+-------------+--------------+------+-----+---------------------+---------------
-+

Although its simple enough to remove the '' when accessing mysql directly, the ADO->ODBC->MySQL path puts quotes around the date without much choice

How to repeat:
set character set utf8;
create table test (mydatetime datetime not null);
insert into test (mydatetime) VALUES ('20040312013912');
select * from test;

Suggested fix:
unknown

[11 Mar 2004 16:01] Dean Ellis

I cannot repeat this using the current 4.1.2 sources; it is possible that this has already been corrected as part of another bug, so you may wish to try to repeat this when 4.1.2 is released.  Thank you.