-1

I have a date/time value in String form like "11/28/2017 4:00:49 PM" in my java code. Now how can I take this same value to MySQL column of type datetime?

Please note, I'm using PreparedStatement to insert the record in MySQL DB.

Here are the few ways I tried...

DateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DateFormat outputFormat = new SimpleDateFormat("MM-dd-yyyy KK:mm:ss a");
ps.setString(outputFormat.format(inputFormat.parse(rs.getString(1))));

I used the above code and I'm getting the below exception...

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '08-09-0017 02:46:57 AM' for column 'column_name' at row 1

If I use the below code, I'm getting only Date but time with AM/PM is lost

ps.setDate(inputFormat.parse(rs.getString(1)));

If I use the below code, I'm getting only Time but without AM/PM and Date

ps.setTime(inputFormat.parse(rs.getString(1).getTime()));

If I use the below code, I get error

ps.setDate(1, (java.sql.Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("11/28/2017 4:00:49 PM"));

java.lang.ClassCastException: java.util.Date cannot be cast to java.sql.Date
Suresh
  • 1,491
  • 2
  • 22
  • 27
  • You could start by [parsing it](https://stackoverflow.com/questions/8746084/string-to-localdate) into a `LocalDatetime`, I already flagged it but this is clearly a duplicate ! – AxelH Dec 08 '17 at 12:01
  • @AxelH Sorry AxelH, I can only use java 1.7 Though I referred LocalDatetime also just for reference but I didn't see how I can get my string value exactly with AM/PM also – Suresh Dec 08 '17 at 12:46
  • Don't worry, there is also an older question for that [here](https://stackoverflow.com/questions/6510724/how-to-convert-java-string-to-date-object) ;) I ave added an answer for your AM/PM problem – AxelH Dec 08 '17 at 12:47
  • Older question is addressed only for Date value and that doesn't address my question – Suresh Dec 08 '17 at 13:01
  • (your edit came too late... didn't notice it before) You need to use the `time` value (in `long`) of the `java.util.Date` to instantiate a `java.sql.Date`, they only share their name, not the type so you can't cast then into each other. See my answer (I am not sure if you can use a `java.sql.Date` so I've also posted the `java.sql.Timestamp` way. – AxelH Dec 08 '17 at 13:14

3 Answers3

1

After several tries, I found that MySQL DB won't accept the 12 hour format for datetime type column.
The Code I used is below shown.

ps.setTimestamp(i, new java.sql.Timestamp(new SimpleDateFormat("MM-dd-yyyy KK:mm:ss a").parse("10-26-2017 2:46:57 PM").getTime()));

Output I got in MySQL DB is as shown below, which is in 24 hour format.

2017-10-26 14:46:57.000000
Suresh
  • 1,491
  • 2
  • 22
  • 27
0

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

    java.sql.Date currentdate=new java.sql.Date(date1.getTime());
    java.sql.Time currenttime=new java.sql.Time(date1.getTime());

prepareStatement.setDate(1,currentDate); prepareStatement.setTime(2,currentTime);

see this code it will current Date and Time

Naresh
  • 1
  • 2
  • How do date1.getTime() in both the lines will give different results as Date & Time? – Suresh Dec 08 '17 at 12:58
  • I don't understand how your code will fit as a solution to my problem, please have a look at my question again and also take a look at my reply down the line where I have provided the input as String format which is converted to Date and later into Timestamp. Anyways, I found the solution, thanks for your interest to reply! – Suresh Dec 18 '17 at 06:25
0

Since you want to parse into a Date your String, and that your problem is the AM/PM, you can see in the SimpleDateFormat documentation :

Letter  Date or Time Component  Presentation    Examples 
a       Am/pm marker            Text            PM

So this should be something like :

String s = "11/28/2017 4:00:49 PM";
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy k:mm:ss a");
java.util.Date myDate = sdf.parse(s);
System.out.println(myDate);

Tue Nov 28 16:00:49 CET 2017

Note that in your code, you are not using a format that match at all

new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
              .parse("11/28/2017 4:00:49 PM"))

For the way to use a java.util.Date in a PreparedStatement, I prefer to let you see that in the complete post : Using setDate in PreparedStatement

But basically, you just need to

ps.setDate(i, new java.sql.Date(myDate.getTime());

or (not sur if Date manage the time part in mysql... it might depends on the jdbc version)

ps.setTimestamp(i, new java.sql.TimeStamp(myDate.getTime());
AxelH
  • 14,325
  • 2
  • 25
  • 55
  • Got the below error if I use the last line you posted... com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '0017-08-09 02:46:57' for column 'column_name' at row 1 – Suresh Dec 08 '17 at 13:27
  • @mannedear Are you sure of the date you have posted ? It seems the `String` looks like `11/28/17 4:00:49 PM` instead. Did you check with the `java.sql.Date` too ? I believe this should work with more recent JDBC version. (I don't have a MySql installed here ... I could checked later maybe...) – AxelH Dec 08 '17 at 13:30
  • @mannedear another solution is too use a `String` with the correct format like [this](https://stackoverflow.com/a/44588850/4391450) but this is not the best... So you would need to parse the `String` into a `Date` and then format it into a `String` of the desired pattern. – AxelH Dec 08 '17 at 13:32
  • ps.setTimestamp(i, new java.sql.Timestamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("11/28/2017 4:00:49 PM").getTime())); This is what I used and it thrown the error which I shared earlier – Suresh Dec 08 '17 at 13:40
  • I tried this new way and I'm almost there but AM/PM only missing in the output, also note that time is displayed in 24 hrs format. Output:2017-10-26 14:46:57.000000 Code I used is ps.setTimestamp(i, new java.sql.Timestamp(new SimpleDateFormat("MM-dd-yyyy KK:mm:ss a").parse("10-26-2017 2:46:57 PM").getTime())); – Suresh Dec 08 '17 at 13:52
  • @mannedear The DB is storing a Date in a specific format that you can't ignore. It is parsing the value into a `String` when you check the value. But the date is correct... You might need to check if this is possible to change the default date format of the DBMS, if not you need to parse the date in your desired format but this is way out of my knowledge ;) **But you are clearly able to store your date into the DB**. What was the solution used (for me to clear the answer ;) ) – AxelH Dec 08 '17 at 13:56