"Incorrect datetime" value in Mysql

Asked

Viewed 2,009 times

7

Hello, I’m trying to enter dates in the format dd/mm/Y in two jTextField, in MYSQL, however I get an error message only for the first field:

Incorrect datetime value: '01/01/2016' for function str_to_date

The java code is :

if (novoCadastro) {
                //Cria a String para inserir os dados
                query = "INSERT INTO Clientes(Nome,Endereço,Cidade,CPF,TelefoneCliente,EmailCliente,Produto,UltimaCompra,ClienteDesde) VALUES(?,?,?,?,?,?,?,DATE_FORMAT(STR_TO_DATE(?, '%dd/%mm/%Y'),'%d-%m-%Y'),DATE_FORMAT(STR_TO_DATE(?,'%dd/%mm/%Y'),'%d-%m-%Y'))";
            //Seta os valores na String de inserção
           System.out.println(txtUltComp.getText());
           System.out.println(txtClienDesd.getText());
            stmt = con.prepareStatement(query);
            stmt.setString(1, txtNome.getText());
            stmt.setString(2, txtEndereco.getText());
            stmt.setString(3, txtCidade.getText());
            stmt.setString(4, txtCPF.getText());
            stmt.setString(5, txtTelefone.getText());
            stmt.setString(6, txtEmail.getText());
            stmt.setString(7, txtProduto.getText());
            stmt.setString(8, txtUltComp.getText());
            stmt.setString(9, txtClienDesd.getText());
            JOptionPane.showMessageDialog(null, "Cadastro realizado com sucesso! ");
mysql

1 answer

6


According to the documentation relating to formatting of dates, a %d already represents the two digits of the day of the month. Similarly, %m already represents the two digits referring to the month itself.

That way, instead of

STR_TO_DATE(?, '%dd/%mm/%Y')

do

STR_TO_DATE(?, '%d/%m/%Y')

  • Okay, I switched to STR_TO_DATE(?, '%d/%m/%Y') and now the msg I got was: Data truncation: Incorrect date value: '01-01-2016' for column 'Ultimacompra' at Row 1

  • @Claudiopedra If the 'Ultimacompra' and 'Clientesince' columns are dates, you cannot use DATE_FORMAT in your query. DATE_FORMAT returns a string. Just keep STR_TO_DATE.

  • Hello Genos, thanks for your attention, but the two fields are Strings in java, but in Mysql are as date

  • 1

    @Claudiopedra as I said earlier, "If the columns 'Ultimacompra' and 'Clientedesde' are DATES[.. ]". DATE_FORMAT is causing you to pass a string to a date column.

  • Thanks for the help friend, I took out the DATE_FORMAT and it worked, hug.

  • 1

    @Claudiopedra kindly accept this answer if she has answered your question.

Show 1 more comment

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.