Java Dateformat Error

Question

I am using JDatechooser netbeans swing plugin for my desktop app development. By default its dateformat is mm/dd/yy but its not the format which the db required. I need to convert it to the yyyy-mm-dd format. I tried with SimpleDateformat class and it gives me the following error: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '8/29/11' for column 'registered_date' at row 1

can anyone give me a good solution to do this properly

Thanks

Answer 1

Try this:

Date date = ...
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String date_for_mysql = df.format(date);

See related SO answers here:

How to parse a date?
Java date formatting?
Converting MySql DateTime type into something more friendly
how to store java date type to mysql date type?
Convert Date into MYSQL Date Format
How to convert Date in String to Sql date?
How to set Current date to MySQL date column from Java?

Answer 2

Your database doesn't require a particular date format at all - you shouldn't be inserting the data as a string in the first place. You should use a prepared statement and use setDate. Ideally you should perform as few string conversions as possible - you don't need one when getting the value out of the JDateChooser, and you don't need one when inserting into the database:

// Unfortunately you need to convert from java.util.Date to java.sql.Date
statement.setDate(1, new java.sql.Date(chooser.getDate().getTime()));

(Note that you do potentially need to bear time zones in mind, but that's a different matter... you can call a setDate overload which takes a calendar too.)

If you've been inserting (or querying) data by constructing a full SQL statement including the values, you should stop doing that right away. Not only do you have this sort of conversion issue, but you also open yourself up to SQL injection attacks. Always use a prepared statement and specify any parameter as parameters rather than including them in the SQL.