CODEWITHSUNDEEP
javasqldatabaseinsert
Erick Reyes
Erick Reyes

Reputation: 85

SQL insert date into database

I use java.util.Date to get the date and I get the date format like this

Sat Jun 29 11:07:25 CDT 2013

When I try to insert it into the database using

String QueryString = "INSERT INTO db (day) Values ('"+d+"');";
    st.executeUpdate(QueryString);

I get this

"Conversion failed when converting date and/or time from character string."

How can I insert that type of date into the db?

Should I declare it as a string?

Upvotes: 0

Views: 1529

Answers (4)

lleon
lleon

Reputation: 2765

You could use SimpleDateFormat to format your Date object 

Date d = Calendar.getInstance().getTime(); //Your date

String dateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(d);

String QueryString = "INSERT INTO db (day) Values ('"+dateString+"');";
st.executeUpdate(QueryString);

Upvotes: 0

hd1
hd1

Reputation: 34657

Alternatively, use an updatable ResultSet:

ResultSet rs = conn.createStatement("select day from db", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs.moveToInsertRow();
rs.updateDate(1, yourJavaSqlDateObject);
rs.insertRow();
rs.first();

Though, depending on your driver/database, there may be a write-lock on the table.

Date formatting should be done on output, not input. Let the database decide how best to store the date.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1499770

how can i insert that type of date into the db? should i declare it as a string?

No - you should avoid even the string conversion you currently have. You shouldn't build your SQL dynamically like that - it's a recipe for SQL injection attacks, hard-to-read code, and conversion failures.

Instead, use a PreparedStatement and set the parameter using setDate:

// TODO: Closing the statement cleanly in a finally block or try-with-resources
PreparedStatement pst = conn.prepareStatement("INSERT INTO db (day) Values (?)");
pst.setDate(1, new java.sql.Date(d.getTime()));
pst.executeUpdate();

Note that java.sql.Date is a subclass of java.util.Date, but they're somewhat different. It's never been clear to me which time zone is used to convert the given instant in time into a real date - and the documentation is less than helpful. It's broken by design in my view, but that's a different matter. You can use another setDate overload which accepts a Calendar - which is used for the time zone. It's still all horribly unclear, but hopefully you can get the result you want.

Upvotes: 4

Juned Ahsan
Juned Ahsan

Reputation: 68715

Use java.sql.Date to be compatible with the databse

Upvotes: 1

Related Questions