MySQL / Java: Invalid date value: '1990' for column
I am facing stubborn problem when entering date in MySQL using Java. In my code, I am converting java.util.Date to java.sql.Date to quickly and easily align SQL formatting for dates.
Below is the code I am using to insert the date into the database. Note. The code is shortened to express your problem.
The client object is passed through function arguments.
//Date to Date
java.sql.Date sqlBirthday = new java.sql.Date(client.getBirthday().getTime());
System.out.println(sqlBirthday); // Prints YYYY-MM-DD
sql = "INSERT INTO clientdata (client_id, weight, height, birthday) VALUES (" + client.getID() + "," + client.getWeight() + "," + client.getHeight().getMeasurementInInches() + ","
+ sqlBirthday + ")";
statement.execute(sql);
statement.close();
After executing the code to enter data into the database, I get the following error at runtime:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Invalid date value: "1990" for column "birthday" on row 1
The printout outputs the following:
1998-05-03
Exit expected.
The stack trace for the error is below, but I doubt it matters much:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '1990' for column 'birthday' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3833)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
at fitness.traingym.client.utils.DatabaseHandler.createLogin(DatabaseHandler.java:71)
at fitness.traingym.client.TrainManagement.testClientCreate(TrainManagement.java:47)
at fitness.traingym.client.TrainManagement.<init>(TrainManagement.java:22)
at fitness.traingym.client.TrainManagement.main(TrainManagement.java:57)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
Interestingly, I ran into this error as the date formatting seems to be correct for the SQL syntax.
source to share
The easiest way to solve the problem is to associate a date string with a quote '
, but this is a nasty trick and SHOULD BE AVOIDED AT ALL . The best way to solve this problem and work with parameterized queries is to use PreparedStatement
s:
sql = "INSERT INTO clientdata (client_id, weight, height, birthday) VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInteger(1, client.getID());
pstmt.setInteger(2, client.getWeight());
pstmt.setInteger(3, client.getHeight());
pstmt.setTimestamp(4, new java.sql.Timestamp(client.getBirthday().getTime()));
pstmt.executeUpdate();
pstmt.close();
source to share
String date = request.getParameter("date");
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); // your template here
java.util.Date dateStr = formatter.parse(sqlBirthday );
java.sql.Date dateDB = new java.sql.Date(dateStr.getTime());
Pass it in this java.sql.Date in a prepared post.
source to share