MySQL filter by date

Issue

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);

Solution

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.

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published