MySQL Bugs: #18394: Don't get any exception with "2005/02/30" with connector/J

Bug #18394	Don't get any exception with "2005/02/30" with connector/J
Submitted:	21 Mar 2006 14:50	Modified:	21 Mar 2006 16:59
Reporter:	Laurent Revel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.12	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
I don't get any exception on invalid date.

Running the following INSERT with MySQL 4.1.12 : 
create table a (a DATE);
insert into a values ('2005/02/30');

if I retrieve it by Java - JDBC running the statement below :
rs = stmt.executeQuery("SELECT a FROM a");
String myDate = rs.GetDate(1);

I get "2005-03-02" but I would like a exception.

How could we set the MySQL JDBC to get exception ?
I seems that setLenient could help to fix this problem ... but MySQL GetDate doesn't accept any parameters like this ? Even the 5.0 JDBC !

How to repeat:
create table a (a DATE);
insert into a values ('2005/02/30');

-- Run java doing
rs = stmt.executeQuery("SELECT a FROM a");
String myDate = rs.GetDate(1);
-- the value retrieved is not the same than the first inserted

Thank you for a problem report. Sorry, but it is not a bug. It is a documented behaviour of MySQL server. Please, read tha manual (http://dev.mysql.com/doc/refman/4.1/en/datetime.html):

"The MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to '0000-00-00'. Please note that this still allows you to store invalid dates such as '2002-04-31'. To ensure that a date is valid, perform a check in your application."

This depends on the correct sql_mode being implemented on your server:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html