4

I have a DatePicker in my FXML and I need the Date to insert it into my SQL-Database. I want to format my Date but it doesn't work.

    LocalDate localDate = purchased_at.getValue();
    localDate.format(DateTimeFormatter.ofPattern("dd.mm.yyyy"));

This is the Error I get.

Caused by: java.time.temporal.UnsupportedTemporalTypeException: Unsupported field: MinuteOfHour

I'm still kind of a beginner. I had Java for the past 3 or 4 months now. I'm trying my best to improve.

Vins
  • 291
  • 1
  • 5
  • 19
  • `mm` probably refers to `minutes`, instead of `months`. You should check the documentation. – Peter Bruins May 22 '18 at 07:27
  • 5
    You should be using `MM` instead of `mm`. – Ben May 22 '18 at 07:27
  • 2
    You also need to pick up the format from the return value from `localDate.format()`. The `LocalDate` itself is immutable and not altered by the call. – Ole V.V. May 22 '18 at 07:29
  • I tried it out with MM instead of mm. Still doesn't work. I also tried it with localDate.format() but IntelliJ tells me format (java.time.format.DateTimeFormatter)in LocalDate cannot be applied to String – Vins May 22 '18 at 07:40
  • Possible near-duplicate of [SimpleDateFormat ignoring month when parsing](https://stackoverflow.com/questions/3056703/simpledateformat-ignoring-month-when-parsing) and of [Getting wrong month when using SimpleDateFormat.parse](https://stackoverflow.com/questions/24141942/getting-wrong-month-when-using-simpledateformat-parse). Also akin to [String to ZonedDateTime is changing format](https://stackoverflow.com/questions/50120213/string-to-zoneddatetime-is-changing-format). – Ole V.V. May 22 '18 at 07:47
  • 2
    `LocalDate ld = LocalDate.now();` and `DateTimeFormatter.ofPattern("dd.MM.yyyy"));` work, so changing the format specifier from `mm` to `MM` should be the way to go – MadProgrammer May 22 '18 at 07:48
  • Don’t format your date for insertion into your database. Just pass it as it is. Your JDBC driver or JPA implementation will take care of everything. – Ole V.V. May 22 '18 at 10:40
  • @whoever voted to close, the issues in the question are non-trivial and IMHO also interesting to other readers. The exception reported is readily reproducible always. – Ole V.V. May 22 '18 at 14:33

2 Answers2

7

Don’t format your date for insertion into your SQL database. Assuming that your database column has datatype date and you are using at least Java 8 and at least JDBC 4.2, just pass the LocalDate to your PreparedStatement as it is:

    PreparedStatement insertStmt = myConnection.prepareStatement(
            "insert into my_table(purchase_date) values (?)");
    insertStmt.setObject(1, purchaseDate);

Your JDBC driver will take care of the rest. If using JPA, your JPA implementation will take care of it too.

If your column has char type (for example varchar(10)) and you cannot change it, don’t invent your own format for it. Store the date in ISO 8601 format. LocalDate.toString() produces this format.

    String formattedDate = purchaseDate.toString();
    System.out.println(formattedDate);

In my case output was:

2017-11-29

As an aside, for presentation to your user you shouldn’t invent your own format either. Rather rely on the built-in formats in Java. For example:

    Locale turkish = Locale.forLanguageTag("tr");
    DateTimeFormatter dateFormatter = DateTimeFormatter.ofLocalizedDate(FormatStyle.SHORT)
            .withLocale(turkish);
    String formattedDate = purchaseDate.format(dateFormatter);
    System.out.println(formattedDate);

Output:

29.11.2017

What went wrong in your code?

There are two things wrong:

  1. You used lowercase mm. This means minute of hour, and since a LocalDate doesn’t have a time of day in it, it threw the exception you saw. The message you got is pretty precise:

Unsupported field: MinuteOfHour

Instead you may use uppercase MM for two-digit month.

  1. You need to pick up the format in the String returned from the format method. The LocalDate is immutable and therefore not affected by the method call. Also it cannot have a format in it. It’s just a date in the calendar.

Links

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • Hi, thanks for the answer! Is there any possible way to insert a date only with the month and the year (05.2018) instead of the full date (22.05.2018) – Vins May 22 '18 at 13:34
  • If the datatype in the database is `date`, it will probably insist on storing a day of month too. You would need to check this with the documentation of the RDBMS. Are you using MySQL? – Ole V.V. May 22 '18 at 13:43
1

I had to use a String converter for my Datepicker. 

    public String changeformat(DatePicker date) {

    date.setConverter(new StringConverter<LocalDate>() {
        String pattern = "MM.yyyy";
        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern(pattern);

        {
            date.setPromptText(pattern.toLowerCase());
        }

        @Override
        public String toString(LocalDate date) {
            if (date != null) {
                return dateFormatter.format(date);
            } else {
                return "";
            }
        }

        @Override
        public LocalDate fromString(String string) {
            if (string != null && !string.isEmpty()) {
                return LocalDate.parse(string, dateFormatter);
            } else {
                return null;
            }
        }
    });
    return null;
}

It worked perfectly fine. I had to use a parameter since I'm currently using 5 Datepickers.

Vins
  • 291
  • 1
  • 5
  • 19