Java: MySQL DATETIME conversion errors?

[Cédric Pillonel]

I do the following in my java application:

Statement s = myConnection.createStatement();
ResultSet rs = s.executeQuery("SELECT startTime FROM MyTable WHERE
_rowid=1");
if (rs.next())
{
java.sql.Timestamp stamp = (java.sql.Timestamp)rs.getObject(1);
String startTime = stamp.toString();
}

I get the following value in startTime: "2006-03-02 12:00:00.0"
But in the database is the following value stored: "2006-02-30 12:00:00.0"

I am running MySQL 4.1.12 on RedHat Linux ES 4.0 EMT64.
I run my java application on Windows 2000 with java 1.5.0_03-b07 and
mysql-connector-java-3.1.12. I have also tried
mysql-connector-java-5.0.0-beta, same problem.

Any idea?
Thank you.

[Brian Wakem]

Cédric Pillonel wrote:

> I do the following in my java application:
>
> Statement s = myConnection.createStatement();
> ResultSet rs = s.executeQuery("SELECT startTime FROM MyTable WHERE
> _rowid=1");
> if (rs.next())
> {
> java.sql.Timestamp stamp = (java.sql.Timestamp)rs.getObject(1);
> String startTime = stamp.toString();
> }
>
> I get the following value in startTime: "2006-03-02 12:00:00.0"
> But in the database is the following value stored: "2006-02-30 12:00:00.0"

No such date as Feb 30th

> I am running MySQL 4.1.12 on RedHat Linux ES 4.0 EMT64.
> I run my java application on Windows 2000 with java 1.5.0_03-b07 and
> mysql-connector-java-3.1.12. I have also tried
> mysql-connector-java-5.0.0-beta, same problem.
>
> Any idea?

It is assuming you mean 2 days after Feb 28th.


--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Bill Karwin]

Brian Wakem wrote:

> It is assuming you mean 2 days after Feb 28th.

Yes. This was reported as a bug
([url]http://bugs.mysql.com/bug.php?id=16147[/url]) and was closed, claiming it is
intentional behavior.

In 5.0, if you try to put in an invalid date, it issues a warning or an
error by default.

SELECT DATE('2006-02-30');
Error 1292 Truncated incorrect datetime value: '2006-02-30'

SET GLOBAL SQL_MODE='ALLOW_INVALID_DATES';
SELECT DATE('2006-02-30');
2006-02-30

In 4.1 and prior releases, MySQL allows invalid dates; day must be
0..31, and month must be 0..12.

See also:
[url]http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html[/url]
[url]http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html[/url]

Regards,
Bill K.