MySQL filter by date-CodePudding

I am building a query string like this.

string query = "SELECT * FROM "    table   " where DATE(Date) > "   howFarBack.ToString("yyyy-MM-dd");

Hwowever, when it executes

while (dataReader.Read())

I am seeing Dates well before the howFarBack ????

    public List<OHLC> Select(string table, System.DateTime howFarBack)
    {
            string query = "SELECT * FROM "    table   " where DATE(Date) > "   howFarBack.ToString("yyyy-MM-dd");

            //Create a list to store the result
            var list = new List<OHLC>();

            //Open connection
            if (OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                
                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    var ohlc = new OHLC();

                    ohlc.Date = (System.DateTime)dataReader[0];
                    ohlc.Open = Math.Round((double)dataReader[1], 2);

CodePudding user response：

When in doubt, try to debug by examining the resulting SQL query, not the C# code that formats the SQL query.

I would guess that your query lacks single-quote delimiters around the date literal. So it is ultimately a query like:

SELECT * FROM MyTable where DATE(Date) > 2021-11-02

But 2021-11-02 isn't a date, it's an arithmetic expression that evaluates to an integer: 2021 minus 11 minus 2 = 2008. This will certainly match a lot of dates you didn't intend it to.

You could solve this by ensuring that the right type of quotes are around your date literal (it's actually a string literal that is interpreted as a date when compared to a date).

SELECT * FROM MyTable where DATE(Date) > '2021-11-02'

But it's far better to use query parameters, as mentioned in the comment above.

SELECT * FROM MyTable where DATE(Date) > @howFarBack

Then you don't need quotes. In fact you must not use quotes around the parameter placeholder.

See Parameterized Query for MySQL with C# or many other references for using parameters in SQL statements in C#.

Also remember that parameters can only be used in place of a single literal value. You can't use parameters for table or column identifiers, or a list of values, or SQL keywords, etc.