CODEWITHSUNDEEP
javajava-time
Code_Ninja
Code_Ninja

Reputation: 1867

how can I insert date in mysql query?

I am trying to save date and time in the attribute of datetime type ('0000-00-00 00:00'). I used the following code. But error - HTTP Status 500 - Internal Error comes up, because of line 6 displaying the following error message: java.time.format.DateTimeParseException: Text '2017-04-30 23:59 could not be parsed at index 10 

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm");
LocalDateTime forDate = LocalDateTime.parse(newDate, formatter);
out.println(forDate);
ps = con.prepareStatement("INSERT INTO reminder_logs VALUES(NULL, ?, ?)");
ps.setInt(1, r_id);                        
ps.setDate(2, forDate);
i = ps.executeUpdate();

Edited: Error - HTTP Status 500 - Internal Error HTTP Status 500 - Internal Error

I tried to use setTimestamp(2, forDate) instead of setDate(2, forDate) but then I got an error saying - incompatible types : LocalDateTime cannot be converted to Timestamp. I tried to take references from the below links but none of them helped:

  1. java.time.format.DateTimeParseException: Text '2016-2-2' could not be parsed at index 5

  2. How to set current date and time using prepared statement?

  3. Unparseable date error on Java

What can I do to solve this error? I am running java se 8.

Upvotes: 0

Views: 243

Answers (1)

Matt Clark
Matt Clark

Reputation: 28599

The reason for the java.time.format.DateTimeParseException is the fact that you are using hh instead of HH in your format string.

From the docs

H       hour-of-day (0-23)          number            0
h       clock-hour-of-am-pm (1-12)  number            12

Following which you give a 24h formatted timestamp.

// runnable example

String newDate = "2017-04-30 23:59";
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm");
LocalDateTime forDate = LocalDateTime.parse(newDate, formatter);
System.out.println(forDate);

Results in:

Exception in thread "main" java.time.format.DateTimeParseException: Text '2017-04-30 23:59' could not be parsed: Invalid value for ClockHourOfAmPm (valid values 1 - 12): 23
    at java.time.format.DateTimeFormatter.createError(Unknown Source)
    at java.time.format.DateTimeFormatter.parse(Unknown Source)
    at java.time.LocalDateTime.parse(Unknown Source)
    at _Scratchpad.SO.main(SO.java:12)
Caused by: java.time.DateTimeException: Invalid value for ClockHourOfAmPm (valid values 1 - 12): 23
....

This trace clearly shows that the time is out of range:

Invalid value for ClockHourOfAmPm (valid values 1 - 12): 23

Upvotes: 2

Related Questions