Java+mySQL: Read Date as String

Question

I try to read out a table and write it into a csv file.

while (rs2.next())
{
    Enumeration<String> en = sqlfields.keys();
    while (en.hasMoreElements())
    {
        String field = en.nextElement();
        String value = rs2.getString(field);
        bw.write(Kapseln + value + Kapseln + Trennzeichen);
    }

    bw.newLine();
    cur++;
}

But if the field is type: DateTime, I get this error message:

java.sql.SQLException: Cannot convert value 
'0000-00-00 00:00:00' from column 12 to TIMESTAMP.

Why Java tries to convert to a Timestamp?

I like to get the value as String => rs2.getString(field)

http://stackoverflow.com/questions/782823/handling-datetime-values-0000-00-00-000000-in-jdbc — Vipul, Sep 27 '12 at 13:00

score 3 · Accepted Answer · answered Sep 27 '12 at 12:51

3

set

zeroDateTimeBehavior="convertToNull"

in your JDBC connection properties. ref: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

EDIT that would make your connection string look like this:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

answered Sep 27 '12 at 12:51

bpgergo

15,669
5
44
68

This is true only if he is using mysql. Probably he is, since mysql is emulating null times with '0000-00-00 00:00:00'. – dan Sep 27 '12 at 12:55
I did not put the 'mysql' tag on the question. – bpgergo Sep 27 '12 at 13:00
@bpgergo:) mysql was tagged in the original post [see](http://stackoverflow.com/posts/12621716/edit/ae37f643-985b-4546-90ce-8148d8b66c2e). – Lion Sep 27 '12 at 13:09

score 0 · Answer 2 · answered Sep 27 '12 at 14:03

The following code is working fine. Check your code with this... The fourth field of the emp table is jDate which is a DateTime data type.

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","test");

        System.out.println("Connection is : " + con);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select * from emp");

        //stmt = con.createStatement();

        while(rs.next()) {
            String value = rs.getString(4);
            System.out.println(value);
        }
        System.out.println("Connection is : " + con);
        con.close();

Java+mySQL: Read Date as String

2 Answers2