Professional Web Applications Themes

Java: MySQL DATETIME conversion errors? - MySQL

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....

  1. #1

    Default Java: MySQL DATETIME conversion errors?

    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.


    Cédric Pillonel Guest

  2. #2

    Default Re: Java: MySQL DATETIME conversion errors?

    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]
    Brian Wakem Guest

  3. #3

    Default Re: Java: MySQL DATETIME conversion errors?

    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.
    Bill Karwin Guest

  4. #4

    Default Re: Java: MySQL DATETIME conversion errors?

    please note mysql time function will fail to work after 19th jan,2038.
    Unregistered Guest

Similar Threads

  1. MySQL datetime record not updating with DW toolbox
    By planethead in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: August 13th, 07:05 PM
  2. Replies: 2
    Last Post: March 7th, 07:18 PM
  3. Translating MySQL timestamp to datetime
    By Joshua Beall in forum PHP Development
    Replies: 1
    Last Post: April 22nd, 02:50 PM
  4. MySQL datetime extraction
    By Yasir Malik in forum PHP Development
    Replies: 1
    Last Post: July 19th, 07:27 PM
  5. [PHP] MySQL datetime extraction
    By David Nicholson in forum PHP Development
    Replies: 2
    Last Post: July 19th, 06:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139