Problems when working with dates in the database using a maskedtextbox in dd/MM/yyyy format

Question

I am using a Maskedtextbox in BR culture to force the user to fill in the date in the correct format (dd/MM/yyyy). I remove the Maskedtextbox string to insert into the database and record information containing the date of this information. My intention is that the user can register some information, can also perform a search for time period by filtering the dates through two Mounthcalendar.

The problem is that apparently the information is being recorded in MM/dd/yyyy format and when performing the search by period, the database returns the dates with the months reversed with the dates.

For example, if I record an event called "Calibration" with the date 02/03/2016 (March 2, 2016) in the database the record is as 3 February 2016. And if I do a search from March 1, 2016 to March 31, 2016, the "Calibration" event does not appear in the results. But, if you do the search in the period between 01 e 29 February 2016 the event appears as if it was registered on 3 February.

I don’t know if I’m wrong, but I think the problem is happening at the time of inserting in the database, I’m trying to convert the string that is typed in Maskedtextbox to MM/dd/yyyy format but I’m not getting it.

I have tried using Datetime.Parseexact but error appears : "String not recognized as valid Datetime."

string dataDoFat = mskData.Text;
System.Globalization.DateTimeStyles none = new System.Globalization.DateTimeStyles();
IFormatProvider format = new System.Globalization.CultureInfo("en-US", true);
DateTime dtime = DateTime.ParseExact(dataDoFat, "MM/dd/yyyy",format);

The code I use to enter the records into the database is this:

            Conexao ca = new Conexao();
            string sql = "";
            sql += " Insert Into SisIndice (FAT, nSerie, idDefeito, DataFat) ";
            sql += " Values ( ";
            sql += " " + txtFat.Text + ", ";
            sql += " '" + txtnSerie.Text + "', ";
            sql += " " + cboDefeito.SelectedValue + ", ";
            sql += " #" + mskData.Text  + "# ";
            sql += " ) ";
            ca.Conectar();
            OleDbCommand cd = new OleDbCommand(sql, ca.cx);
            try
            {
                cd.ExecuteNonQuery();
                MessageBox.Show("Ocorrência registrada.");
            }
            catch (OleDbException x)
            {
                MessageBox.Show("Erro: " + x.Message);
            }
            ca.Desconectar();
            LimparCampos();

And what I use to read the database records in the time period chosen by the user is this:

        string DataInicio = mcInicio.SelectionStart.Date.ToString("MM/dd/yyyy");
        string DataFim = mcFim.SelectionStart.Date.ToString("MM/dd/yyyy");

        Conexao ca = new Conexao();
        string sql = "";
        sql += " Select ";
        sql += " s.idProduto, s.FAT, s.nSerie, d.Defeito, s.DataFat ";
        sql += " From ";
        sql += " (SisIndice s INNER JOIN ";
        sql += " Defeitos d ON s.idDefeito = d.idDefeito) ";
        sql += " WHERE(((s.DataFat) BETWEEN ";
        sql += " #" + DataInicio + "# ";
        sql += " and ";
        sql += " #" + DataFim + "#)) ";
        ca.Conectar();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, ca.cx);
        DataSet ds = new DataSet();
        da.Fill(ds, "SisIndice");

        dgvTestGraf.DataSource = ds.Tables["SisIndice"];
        ca.Desconectar();

Really the solution is to convert the date format inserted into the database? What is the best way to do this?

Thanks in advance!

Answer 1

Leandro,

Probably it is reversing the day and month in time to insert.

The best way to work with date is to send the parameter in your command.

Command.Parameters.Add(new System.Data.OleDb.OleDbParameter("nome","valor"));

Using this way, Oledb itself will convert to the correct format.

In addition to making code safer by avoiding SQL Injection.