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

Question

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

Navigation

#1 by (0 votes)

0

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 do a select that filters as follows: SET PARAMETER 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 make? Thankful

I'm 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();
    }

mysql sql c# select

asked by anonymous 26.01.2015 / 17:30

1 answer

Is it possible to select a video card? Distribute applications with Mysql

score 0 · Accepted Answer

If the column in the database supports time, minutes, and seconds, the date recorded there may actually contain these values, and you are searching for a date without these values. So the bank might look like this:

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

And, by not reporting the time, you're actually looking for:

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

Option:

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

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

Some comments:

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 when being converted to DateTime it can result in a different date.

The ideal would be to be able to receive the date as DateTime , hence you would report it to the SELECT as a typed parameter instead of concatenating a string.