Select Format yyyyyy-MM-dd HH:mm:ss in MYSQL table column

Question

Date

2014-12-01 10:52:38
2014-12-01 10:52:14
2014-12-01 10:51:57
2014-12-01 10:35:34
2014-12-01 10:23:17
2014-12-01 10:22:18
2014-12-01 09:03:49
2014-12-01 08:53:51

I have this column with these dates and I need to select it as follows: send PARAMETRO DOM aaaaMMDdd with value already specified and I want the routine to bring me only the dates of a day. The difficulty is that the column is in the format yyyy-MM-dd HH:mm:ss and the select below does not bring me anything. How to do? Grateful

I am working with c# winform and MYSQL. att Marcos

    static public string SelectQuantidadeRenavan(MySqlConnection db, string renavam,string datarecebe)
    {
        string strdata = Convert.ToDateTime(datarecebe).ToString("yyyy-MM-dd");

        StringBuilder selectQuantidadeRenavam = new StringBuilder();

        selectQuantidadeRenavam.AppendFormat("select count(RENAVAM)");
        selectQuantidadeRenavam.AppendFormat(" from numtermo ");
        selectQuantidadeRenavam.AppendFormat("WHERE ");
        //selectQuantidadeRenavam.AppendFormat("RENAVAM = 1) {0}", renavam);
        selectQuantidadeRenavam.AppendFormat("RENAVAM =  {0} and Data = '{1}'", renavam, strdata) 



        MySqlCommand MysqlResult = new MySqlCommand(selectQuantidadeRenavam.ToString(), db);

        return MysqlResult.ExecuteScalar().ToString();
    }

Answer 1

If the column in the database admits time, minutes and seconds, the date recorded there may actually contain these values, and you are looking for a date without these values. So the bank could be like this:

Data = 2014-01-26 11:22:44

And by not telling the time, in practice you are looking for:

Data = 2014-01-26 00:00:00

Option:

selectQuantidadeRenavam.AppendFormat("RENAVAM =  {0} and Date(Data) = '{1}'", renavam, strdata);

In the above code the function Date Mysql truncates the date in the database to midnight before comparing to its date, which is also like midnight.

Some remarks:

You are converting a string to date (Convert.ToDateTime(datarecebe)) and then to string again ().ToString("yyyy-MM-dd")). I understand that this is to convert the date format to that accepted by the bank, but it seems strange. Using this way, also make sure that the date in the string datarecebe is even coming in the format of the local culture, or by being converted to Datetime it may result in a different date.

Ideally you could receive the date as Datetime, then you would inform it to SELECT as a typed parameter instead of concatenating a string.