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?
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.
Use java.sql.Date to be compatible with the databse
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.
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);
