Data truncation error when adding date(using JCalendar) in MySQL

Question

I am inserting data into a form to MySQL database but when I am setting a date in the date field(using JCalendar/JDateChooser) in the form, I am getting a Data Truncation error like Got an exception! Data truncation: Incorrect date value: 'Mar 10, 2016' for column 'arrival_date' at row 1 as the date I entered was that of Mar 10, 2016.

Here is my code:

             Connection conn = null;

            // the mysql insert statement
         String insertTable = "INSERT INTO booking" + 
         "(fname,lname,contact,email,address, num_persons, arrival_date,departure_date,arrival_time,room_type,view_type,roomno,room_facility )VALUES" 
         + "(? , ? , ? , ?,? , ? , ? , ?,? , ? , ? , ?,?)";

            try {

                // create a mysql database connection
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                String connectionUrl = "jdbc:mysql://localhost:3306/testing?autoReconnect=true&useSSL=false";
                String connectionUser = "root";
                String connectionPassword = "admin";
                conn = (Connection) DriverManager.getConnection(connectionUrl, connectionUser,connectionPassword);

                // create the mysql insert preparedstatement
                PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(insertTable);

                preparedStmt.setString(1,fnameField.getText() );
                preparedStmt.setString(2,(lnameField.getText()));
                preparedStmt.setString(3,(contactField.getText()));
                preparedStmt.setString(4,(emailField.getText()));
                preparedStmt.setString(5,(txtadd.getText()));
                preparedStmt.setString(6,(spinner.getValue().toString()));
                preparedStmt.setString(7,((JTextField)dateChooser1.getDateEditor().getUiComponent()).getText());
                preparedStmt.setString(8,((JTextField)dateChooser2.getDateEditor().getUiComponent()).getText());
                preparedStmt.setString(9,(timeSpinner.getValue().toString()));
                preparedStmt.setString(10,rtype);
                preparedStmt.setString(11,vtype);
                preparedStmt.setString(12,(txtroomnum.getText()));
                preparedStmt.setString(13,room_facility);

             // execute the preparedstatement
                preparedStmt.executeUpdate();
                conn.close();

And here is my table for the database:

 create table booking(
booking_id int unsigned auto_increment not null,
fname varchar(50) not null,
lname varchar(50) not null,
contact int,
email varchar(75),
address varchar(200),
num_persons int,
arrival_date date,
departure_date date,
arrival_time time,
room_type tinyint(1) not null,//for radiobuttons
view_type tinyint(1) not null,//for radiobuttons
roomno int,
room_facility tinyint(1) not null,//for multiple selection of checkboxes(not sure if the data type is correct here)
primary key(booking_id)
);

Here is my form to show the fields and the format in which the date appears :

MadProgrammer · Accepted Answer · 2016-03-04 23:38:16Z

2

First of all, don't do preparedStmt.setString(7,((JTextField)dateChooser1.getDateEditor().getUiComponent()).getText());.

Seriously, if you're going to store date/time data in a database, start by using the appropriate data type for the column.

Start by getting the Date value from the editor...

java.util.Date utilDate = dateChooser1.getDate();

Then wrap it in a java.sql.Date

java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

Then use PreparedStatement#setDate to bind the value to the query...

preparedStmt.setDate(7, sqlDate);

Then let the JDBC driver figure it out.

There are any number of good reasons for doing this, apart from storing data in the most appropriate format, you can know query the database using it's own date/time functionality and you no longer care about the format the database "might" use to store the data

edited Mar 4, 2016 at 23:38

answered Mar 4, 2016 at 23:07

MadProgrammer

348k22 gold badges243 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

24 Comments

Tia Over a year ago

Shouldn't it be utilDate.getTime()?

MadProgrammer Over a year ago

Then your next problem is column 9, you need to make the spinner value a java.sql.Time

MadProgrammer Over a year ago

You need to take the spinners value and convert it to a long which represetns the number of milliseconds since midnight, this value you then pass to java.sql.Time before binding it to your PreparedStatement

MadProgrammer Over a year ago

So 2:14am is pretty meaningless to the database, instead, you need to convert this to milliseconds since midnight (which is what the Time class works in), so you'd need to break the String down into hours and minutes and convert these to milliseconds...of course, you could just a SimpleDateFormat which would give you a Date object, from which you could use getTime which would return you the value you need

MadProgrammer Over a year ago

Let's just say, that's just basic programming, and if you have trouble grasping that, then you have bigger problems to deal with and should probably put GUI programming on hold. It's a bit hard to determine based on your code snippet, but if your JSpinner is configured to use Date, you could use new java.sql.Timer(((java.util.Date)timeSpinner.getValue()).getTime()); which again is an example of you not understanding the basic API

|

Jens · Accepted Answer · 2016-03-04 23:02:38Z

1

You have to convert the date from your dateChooser to a java.util.Date using simpleDateFormat. And after that you have to use setDate to write it o the db. Parse the date:

DateFormat df = new SimpleDateFormat("MMM dd','yyyy");
preparedStmt.setDate(7,df.parse(((JTextField)dateChooser1.getDateEditor().getUiComponent()).getText()));

edited Mar 4, 2016 at 23:02

answered Mar 4, 2016 at 22:42

Jens

69.6k15 gold badges107 silver badges133 bronze badges

9 Comments

Jens Over a year ago

@kaanyılmaz You wrote the Code for formating as string. What is wrong in this case.

Tia Over a year ago

I got an exception message: Got an exception! Unparseable date: "Mar 10, 2016"

Y.Kaan Yılmaz Over a year ago

@Jens it is not hard to write some sql insertion codes. I was trying to say " you can do it on your own" because it is not hard to do even if you know a little bit.

Jens Over a year ago

@Diksha sorry i had a blank to much

Jens Over a year ago

@kaanyılmaz But your Suggestion is wrong. OP should not change the date Format. he has to set the value as date

|

Community · Accepted Answer · 2017-05-23 12:07:50Z

0

Diksha, you are using wrong format for your database.

Mar 10, 2016 this is not acceptable for MySQL. Please use YYYY-MM-DD HH:MM:SS format.

Resource: http://dev.mysql.com/doc/refman/5.7/en/datetime.html

java.util.Date dt = new java.util.Date();

java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String currentTime = sdf.format(dt);

currentTime will give you the default date format of MySQL (current date)

if you wish the change your own dates(like getting dates from your frame) to MySQL date format you could you use this answer https://stackoverflow.com/questions/21879382/convert-string-to-mysql-date-format

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Mar 4, 2016 at 22:34

Y.Kaan Yılmaz

6145 silver badges18 bronze badges

3 Comments

Tia Over a year ago

Thanks! By the way, I am not sure if the data type in my database for multiple selection of checkboxes is correct. I wrote room_facility tinyint(1) not null

Y.Kaan Yılmaz Over a year ago

@Diksha if it helps you in right way, please mark it as an answer.

Y.Kaan Yılmaz Over a year ago

@Diksha I don't think it will make a big difference. Just try out and let us know.

Collectives™ on Stack Overflow

Data truncation error when adding date(using JCalendar) in MySQL

3 Answers 3

24 Comments

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

24 Comments

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related