locked
Insert date on database got ERROR [HY000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.21]Incorrect datetime value: '23/12/2018 12:04' RRS feed

  • Question

  • User-909867351 posted

    Hi

    I try to edit one record with a textField (textBox) with datetime

    If I click in this field (textbox) and update the record everything works fine, but if I don't click in this text and try to save date got the error

    ERROR [HY000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.21]Incorrect datetime value: '23/12/2018 12:04:00' for column 'data_intervencao' at row 1

    Any help?

    I'm using 

    datetpicker control

    https://xdsoft.net/jqplugins/datetimepicker/

    Thank you

    My code:

     SqlDataSource300.UpdateParameters.Clear();
            SqlDataSource300.UpdateCommand = "update clientes set nome=?,morada=?,email=?,localidade=?,postal=?,telefone1=?,telefone2=?," +
                "modelo=?,data=?,serie=?,descricao=?,acessorios=?,data_intervencao=?, vendedor=? where id=?";
            SqlDataSource300.UpdateParameters.Add("@a", txtNome.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtMorada.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtEmail.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtLocalidade.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtPostal.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtTelefone1.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtTelefone2.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtModelo.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtData.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtSerie.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtDescricao.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtAcessorios.Text);
            SqlDataSource300.UpdateParameters.Add("@b", datetimepicker.Text);
            SqlDataSource300.UpdateParameters.Add("@a", txtVendedor.Text);
            SqlDataSource300.UpdateParameters.Add("@a", Request.QueryString["id_cliente"].ToString());
            SqlDataSource300.Update();

    Sunday, December 23, 2018 12:07 PM

Answers

  • User475983607 posted

    Your passing an invalid date to the SQL due to culture settings.  Plus you're not validating the user inputs.    Try first converting the input string to a DateTime then pass the DateTime as a parameter.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 23, 2018 1:01 PM
  • User-893317190 posted

    Hi mariolopes,

    As the error message shows,you should have a right datetime format.

    Right datetime format is like 2018/12/23 12:04:00 or  2018-12-23 12:04:00.

    It should be year/month/day hour:minute:second. Year is first , then month and then day.

    Please change the order of your year, month and day.

    I find the code below could change the datetime format using your plugin

    jQuery('#datetimepicker').datetimepicker({
      format:'DD.MM.YYYY h:mm a',
      formatTime:'h:mm a',
      formatDate:'DD.MM.YYYY'
    });

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 24, 2018 2:34 AM

All replies

  • User475983607 posted

    Your passing an invalid date to the SQL due to culture settings.  Plus you're not validating the user inputs.    Try first converting the input string to a DateTime then pass the DateTime as a parameter.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 23, 2018 1:01 PM
  • User-893317190 posted

    Hi mariolopes,

    As the error message shows,you should have a right datetime format.

    Right datetime format is like 2018/12/23 12:04:00 or  2018-12-23 12:04:00.

    It should be year/month/day hour:minute:second. Year is first , then month and then day.

    Please change the order of your year, month and day.

    I find the code below could change the datetime format using your plugin

    jQuery('#datetimepicker').datetimepicker({
      format:'DD.MM.YYYY h:mm a',
      formatTime:'h:mm a',
      formatDate:'DD.MM.YYYY'
    });

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 24, 2018 2:34 AM