how can I insert date in mysql query?

Question

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

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:

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

but newDate is coming from another jsp page in yyyy-MM-dd HH:mm format.. — Code_Ninja
– Code_Ninja, Commented Apr 28, 2017 at 0:27
The MySQL datetime field includes the seconds, so do what @tadman suggested, and append :00 to your format string. — Matt Clark
– Matt Clark, Commented Apr 28, 2017 at 0:33

Matt Clark · Accepted Answer · 2017-04-28 00:46:24Z

2

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

answered Apr 28, 2017 at 0:46

Matt Clark

28.7k20 gold badges78 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Code_Ninja Over a year ago

Even after editing DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm") to DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"), the error in the above image is still persistent.

Collectives™ on Stack Overflow

how can I insert date in mysql query?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related