How to convert a date format to mysql date format?

Question

I have bunch of dates in a format like Jan. 14,2014,Apr. 20,2014,Aug. 14,2014 etc,. which are extracted from a set of PDF documents.

My Problem
I added the above dates to a mysql column with Column Datatype as Date using java (by PreparedStatement).

....
st.SetDate(3,"Jan. 14,2014");
....

I added the datas using ExecuteQuery.

But when program executes an error message returned by MySql stating that the date formats are incompatible with MySql column type) Date..

My Question
How to convert this above mentioned date formats into mysql compatible Date formats ?

That code wouldn't even compile, as far as I can see - `PreparedStatement.setDate` takes a `Date`, not a `String`. — Jon Skeet, Feb 02 '14 at 08:11

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

By your current posted code:

st.SetDate(3,"Jan. 14,2014");

This does not even compile. You could try getting a new Date object from a String (since this is what you're trying to accomplish), so use a SimpleDateFormat for this:

SimpleDateFormat sdf = new SimpleDateFormat("MMM. dd,yyyy");
Date date = sdf.parse("Jan. 14,2014");
st.setDate(3, new java.sql.Date(date.getTime()));
//rest of your code...

Similar to this, you can parse time or date and time into a java.util.Date using SimpleDateFormat and then convert it to the respective class java.sql.Time and java.sql.Timestamp using date.getTime().

Also note that you can p̶a̶s̶s̶ retrieve a java.util.Date object reference to PreparedStatement#getDate (and getTime and getTimestamp) since java.sql.Date, java.sql.Time and java.sql.Timestamp extend from java.util.Date. For more info, please refer here: Date vs TimeStamp vs calendar?

edited May 23 '17 at 11:56

Community

1
1

answered Feb 02 '14 at 08:24

Luiggi Mendoza

85,076
16
154
332

1

I don't think that last statement make sense. PreparedStatement#setDate expects a java.sql.Date, making java.util.Date incompatible – MadProgrammer Feb 02 '14 at 08:32
1

@MadProgrammer you're right, I got confused with `PreparedStatement#getDate` =\. Editing my answer (and ashamed). – Luiggi Mendoza Feb 02 '14 at 08:38
I had to go look it up myself to be sure :P – MadProgrammer Feb 02 '14 at 08:39
@MadProgrammer answer edited (and hopefully with no more misleadings). – Luiggi Mendoza Feb 02 '14 at 08:43

MadProgrammer · Answer 2 · 2014-02-02T08:36:45.637

2

Assuming the column type supports a Date value, you could use a SimpleDateFormat to parse the String values to a java.util.Date and create a java.sql.Date which can be applied to the setDate method...

SimpleDateFormat sdf = new SimpleDateFormat("MMM. dd,yyyy");
Date date = sdf.parse("Jan. 14,2014");
java.sql.Date sqlDate = new java.sql.Date(date.getTime());

Check it SimpleDateFormat for more details

edited Feb 02 '14 at 08:36

answered Feb 02 '14 at 08:24

MadProgrammer

343,457
22
230
366

Seems like we got the same idea. – Luiggi Mendoza Feb 02 '14 at 08:25

score 0 · Answer 3 · answered Feb 02 '14 at 08:17

0

One possible solution is to use the String datatype instead of date in your table.

answered Feb 02 '14 at 08:17

surya singh

277
6
23

1

That's a very bad idea. – Luiggi Mendoza Feb 02 '14 at 08:19

Hannes · Answer 4 · 2014-02-02T08:24:11.040

0

Use SimpleDateFormat to get the string representation of the date to a Date Object. This date object can then be used to feed the set date method of the prepared statement.

SimpleDateFormat sdf = new SimpleDateFormat(....)
java.util.Date date = sdf.parse(....);

preparedStmt.setDate(...., date);

edited Feb 02 '14 at 08:24

answered Feb 02 '14 at 08:19

Hannes

2,018
25
32

Girish · Answer 5 · 2014-02-02T08:31:36.883

0

first convert the java.util.Date to java.sql.Date then try to set the Java.sql.Date

you can use this logic to convert

If your date is String then first convert it into Java.util.Date type either by using the SimpleDateFormat or DateFormat

If u want to use a DateFormat you can use it also: But this changes the expected date format depending on the locale settings of the machine it's running on.

If you have a specific date format, you can use SimpleDateFormat:

 Date d = new SimpleDateFormat("MMM. dd,yyyy").parse("Jan. 14,2014");

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

edited Feb 02 '14 at 08:31

answered Feb 02 '14 at 08:19

Girish

1,717
1
18
30

`java.sql.Date` extends from `java.util.Date`, so just pass a `java.util.Date` object reference, there's no need to perform all this logic. – Luiggi Mendoza Feb 02 '14 at 08:20
@LuiggiMendoza I could be wrong abut to, but PreparedStatement#setDate expected a java.sql.Date, making java.util.Date incompatible, but you can pass java.sql.Date to methods that expect java.util.Date – MadProgrammer Feb 02 '14 at 08:31
@MadProgrammer yes java.sql.Date extends java.util.Date my friend – Girish Feb 02 '14 at 08:33
@girishlalwani2010 But that doesn't mean that `java.util.Date` can be treated can be treated as a `java.sql.Date` ... or are you agreeing with me (it's like 35 degrees and my brain has melted ;)) – MadProgrammer Feb 02 '14 at 08:35
@Ragesh D Antony have you got that – Girish Feb 02 '14 at 08:39
Yeah, that was for `getDate`, not `setDate`, my vision is blurry at this time of the night =\ – Luiggi Mendoza Feb 02 '14 at 08:44

score 0 · Answer 6 · answered Feb 03 '14 at 21:46

0

I don't really think this all java stuff is necessary. You can simply use this very easy sql process to insert date: NOW() in mysql query like INSERT INTO table_name(c1,c2,datecolumn) VALUES('value1','value2',NOW()); It is much simplier I think :D

answered Feb 03 '14 at 21:46

Krisztián Dudás

856
10
22

How to convert a date format to mysql date format?

6 Answers6