Incorrect date value: 'd. MMM y' for column 'Birthday' at row 1 - MySQL exception

Question

When i insert data in my java eclipse program and try to insert it to the database i get this error:

I have already installed MySQL connector/J and made the database in MySQL workbench.

    Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
feb. 03, 2021 2:31:18 PM numerologi.views.Make$5 actionPerformed
SEVERE: null
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: 'd. MMM y' for column 'Birthday' at row 1

What does this mean and how do i fix it? please help!

is it the " pst.setString(3, dateChooser.getDateFormatString().toString()); " ?

my code:

 dateChooser = new JDateChooser();
  dateChooser.setBounds(140, 40, 500, 20);
  panel.add(dateChooser);
  
  UpdateButton = new JButton("Update");
  UpdateButton.setFont(new Font("Tahoma", Font.BOLD, 12));
  UpdateButton.setBounds(805, 40, 85, 20);
  panel.add(UpdateButton);
  
  DeleteButton = new JButton("Delete");
  DeleteButton.setFont(new Font("Tahoma", Font.BOLD, 12));
  DeleteButton.setBounds(805, 70, 85, 20);
  panel.add(DeleteButton);
  
  ResetButton = new JButton("Reset");
  ResetButton.setFont(new Font("Tahoma", Font.BOLD, 12));
  ResetButton.setBounds(805, 100, 85, 20);
  panel.add(ResetButton);
  
  scrollPane = new JScrollPane();
  scrollPane.setBounds(10, 154, 880, 286);
  panel.add(scrollPane);
  
  table = new JTable();
  table.setBackground(Color.WHITE);
  model = new DefaultTableModel();
  Object[] column = {"Nr","Name","Birthday","Description","Other"};
  Object[] row = new Object[0];
  model.setColumnIdentifiers(column);
  table.setModel(model);
  scrollPane.setViewportView(table);
  
 
//This method contains all codes for update of Database. //

private void upDateDB() {
  try { 
  Class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/num clients", "root", "");
  pst = con.prepareStatement("SELECT * FROM client");
      
  rs =pst.executeQuery();
  ResultSetMetaData StData = rs.getMetaData();
      
  q = StData.getColumnCount();
      
  DefaultTableModel RecordTable = (DefaultTableModel)table.getModel();
  RecordTable.setRowCount(0);
      
  while(rs.next()){
     Vector columnData = new Vector();
          
  for (i = 1; i <= q; i++) {
     columnData.add(rs.getString("Nr"));
     columnData.add(rs.getString("Name"));
     columnData.add(rs.getString("Birthday"));
     columnData.add(rs.getString("Description"));
     columnData.add(rs.getString("Other"));
}
     RecordTable.addRow(columnData);                
          
}} catch (Exception ex) {
     JOptionPane.showMessageDialog(null, ex);
} 

      }


//This method contains all codes for creating events. //

private void createEvents() {
 
  NewButton.addActionListener(new ActionListener() { 
  public void actionPerformed(ActionEvent e) {
  try { 
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost/num clients", "root", "");   
      pst = con.prepareStatement("INSERT INTO client (Nr, Name, Birthday, Description, Other) VALUES (?,?,?,?,?)");

      pst.setInt(1,Integer.valueOf(Tnr.getText()));
      pst.setString(2, Tname.getText()); 
      //pst.setString(2, CBunknown.getActionCommand().toString()); - how to insert Checkbox here? if i want name to be unknown?
      pst.setString(3, dateChooser.getDateFormatString().toString());
      pst.setString(4, Tdescription.getText());
      pst.setString(5, Tother.getText());
      
      pst.executeUpdate();
      JOptionPane.showMessageDialog(null, "Saved");
      upDateDB();
      
  }catch (ClassNotFoundException ex) { 
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
  }catch (SQLException ex) {
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE,null, ex);
      
  }catch (Exception ex){
      JOptionPane.showMessageDialog(null, ex); 
  }}
});
  
  UpdateButton.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
  try { 
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost/num clients", "root", "");   
      pst = con.prepareStatement("UPDATE client SET Nr=?,Name=?,Birthday=?,Description=?,Other=? WHERE Nr = ? ");
      pst.setString(1, Tnr.getText());
      pst.setString(2, Tname.getText()); 
      //pst.setString(2, CBunknown.getActionCommand().toString()); - how to insert Checkbox here? if i want name to be unknown?
      pst.setString(3, dateChooser.getDateFormatString().toString());
      pst.setString(4, Tdescription.getText());
      pst.setString(5, Tother.getText());
      pst.executeUpdate();
      JOptionPane.showMessageDialog(null, "Updated");
      upDateDB();
          
  }catch (ClassNotFoundException ex) {
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level. SEVERE,null, ex);
  }catch (SQLException ex) {             
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE,null, ex);
                  
  }catch (Exception ex){
      JOptionPane.showMessageDialog(null, ex); 
  }}
});
  
  DeleteButton.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
  DefaultTableModel RecordTable = (DefaultTableModel)table.getModel();
  int SelectedRows = table.getSelectedRow();
          
  try{
      Class.forName("com.mysql.jdbc.Driver");
      id = Integer.parseInt(RecordTable.getValueAt(SelectedRows, 0).toString());
      deleteItem = JOptionPane.showConfirmDialog(null,"Do you want to delete client?", "warning",JOptionPane.YES_NO_OPTION);
      
  if (deleteItem ==JOptionPane.YES_OPTION ) {}
      con = DriverManager.getConnection("jdbc:mysql://localhost/num clients", "root", "");   
      pst = con.prepareStatement("DELETE FROM client WHERE Nr = ? ");
      pst.setInt(1, id);
      pst.executeUpdate();
      JOptionPane.showMessageDialog(null, "Deleted");
      upDateDB();
      
      Tnr.setText(" ");
      Tname.setText("");
      Tdescription.setText("");
      Tother.setText("");
      dateChooser.setToolTipText("");
  
  }catch (ClassNotFoundException ex) {
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE,null, ex);
  }catch (SQLException ex) {
      System.err.println(ex);
              
  }catch (Exception ex){
      JOptionPane.showMessageDialog(null, ex); 
  }}  
});
  
  ResetButton.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
      Tnr.setText(" ");
      Tname.setText(" ");
      Tdescription.setText(" ");
      Tother.setText(" ");
      }
});
  
  table.addMouseListener(new MouseListener() {
  public void mouseClicked(MouseEvent e) {
  DefaultTableModel RecordTable = (DefaultTableModel)table.getModel();
  int SelectedRows = table.getSelectedRow();   
      
     Tnr.setText(RecordTable.getValueAt(SelectedRows, 1).toString());
     Tname.setText(RecordTable.getValueAt(SelectedRows, 2).toString());
     dateChooser.setToolTipText(RecordTable.getValueAt(SelectedRows, 3).toString());
     Tdescription.setText(RecordTable.getValueAt(SelectedRows, 4).toString());
     Tother.setText(RecordTable.getValueAt(SelectedRows, 5).toString());
      
}
      
});
  table.addMouseListener(new MouseAdapter() {
  public void mouseClicked(MouseEvent evt) {
  mouseClicked(evt); }
});
      }
                      }

It's the formatting of the date, most likely from the line you are already suspecting... The pattern MySQL is complaining about obviously has a single digit for a day of month (which may be a problem but isn't necessarily one) and a single one for the year (which is more likely to be invalid). How does an example return value of `dateChooser.getDateFormatString().toString()` look like and is it really necessary to call the `toString()` method after calling `getDateFormatString()`? Maybe that already messes up the formatting. Oh, and when it comes to names of months, the language will matter. — deHaar, Feb 03 '21 at 14:43
At the end you should never safe localized date formats in a database. Always use a standard format like 2021-10-23 14:55:55 for mySQL and store it in a date time field — Thallius, Feb 03 '21 at 15:13
@deHaar i tried to delete toString() and nothing changes. I'm a such a green child for code, so i learn every day and some things just doesn't make sense for me. sorry! The name "fødselsdag" is just birthday in Danish, not a month. — Ellinoir, Feb 03 '21 at 16:56
@ClausBönnhoff i am not sure if i know what you mean. I am really green at this! Sorry — Ellinoir, Feb 03 '21 at 16:57
Ok if you are pretty new to coding. Please take the first advice and write your code completely in English. That makes it easier for all of us :) — Thallius, Feb 03 '21 at 17:02
Second one, we do not know what kind of column the birthday is in your database. Normally you should use a date column for dates and in that case you can only store dates in the format 2021-12-23 and not what you try to do — Thallius, Feb 03 '21 at 17:04
It’s fine to write your code in your national language (seems to be Norwegian or Danish in this case). I am supposed to write code in Danish in my workplace. When posting to Stack Overflow, please [create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) in English. You have given us *a lot* of code. Can you leave out the Swing stuff and reproduce the same problem in a 10 lines (or shorter) example program? — Ole V.V., Feb 03 '21 at 18:29
@ClausBönnhoff the code is updated in English now. Is it more readable now? :) And i dont know how to change/edit the format of the DateChooser? — Ellinoir, Feb 03 '21 at 18:55
@OleV.V. yes it is Danish, should be updated in English now. I dont know where the problem is, so i thought 'better safe, than sorry' and entered it all — Ellinoir, Feb 03 '21 at 18:58
We’re happy that you’re on Stack Overflow. If you stay around, consider learning how to prepare a [Short, Self Contained, Correct (Compilable), Example](http://sscce.org/). It may seem like a lot of trouble. However, you will be surprised how often that process helps you solve your own problem before you get around to posting your question at all. And when you do post a question containing that SSCCE, you have taken the trouble that your readers otherwise need to take, which means that your questions will be received a lot better, and you will have better changes of answers and better answers. — Ole V.V., Feb 04 '21 at 17:41

score 2 · Answer 1 · edited Feb 05 '21 at 06:36

java.time and JDBC 4.2

The code below has not been tested, but I think that it works. I basically recommend that you use java.time, the modern Java date and time API, for your date work. JDBC 4.2, which is part of Java SE 8, fully supports java.time types.

Get date from your JDateChooser. Unfortunately JDateChooser is old and not upgraded to java.time, so only supports the poorly designed and long-outdated classes Date and Calendar.

Date oldfashionedDateObject = dateChooser.getDate();

Convert to a modern LocalDate. A LocalDate is a date without time of day and without a timezone.

LocalDate chosenDate = oldfashionedDateObject.toInstant()
        .atZone(ZoneId.systemDefault())
        .toLocalDate();

Pass the date to your prepared statement:

pst.setObject(3, chosenDate);

Neither in Java nor in your database process nor keep your dates as strings. Use date in SQL and LocalDate in Java. This relieves you of all concern about the format of the date. Neither any parsing nor any formatting is called for.

What went wrong in your code?

You called JDateChooser.getDateFormatString(). The method gives you the format string that your JDateChooser uses for formatting the date when showing to the user, in your case d. MMM y. Not the chosen date itself. So you get the same string no matter which date the user chooses. Next, you try to save the string d. MMM y as a date to the database. Since this is not a valid date by any measure, saving fails with the exception you saw.

Links

Oracle tutorial: Date Time explaining how to use java.time.
Related question: Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
Documentation of JDateChooser.getDateFormatString()

I tried your suggestion and i dont get any errors, but i get this message dialog when i run the program after clicking on the add button: java.lang.NumberFormatException: For input string: "". Also the typed information i Jtextfield and dateChooser is not listed in mySQL database. I feel like a complete new dummie here, sorry! — Ellinoir, Feb 04 '21 at 11:40
One possible reason for the behaviour you describe would be if you forgot to type a number in the `Tnr` field before pressing the button. I should say that it’s a new question, so please post it as such. — Ole V.V., Feb 04 '21 at 17:37

Incorrect date value: 'd. MMM y' for column 'Birthday' at row 1 - MySQL exception

1 Answers1

java.time and JDBC 4.2

What went wrong in your code?

Links