CODEWITHSUNDEEP
c#winforms
PJW
PJW

Reputation: 5417

SQL DateDiff inconsistency

When I run the following script on my SQL database (from the management studio) I get the results I expect -

SELECT * 
FROM [Case] 
WHERE ABS((DATEDIFF(DAY, [DateAccident], '2013-01-01'))) < 100;

When I increase / decrease the value 100, I get more / less matches exactly as expected.

However, when I attempt to produce the same result from my WinForms app (in C#) I get far more results than I should -

public static DataTable DOACases(DateTime doa, int days)
{
    try
    {
        DataTable table = new DataTable();
        string sqlText = "SELECT * " +
                         "FROM [Case] " +
                         "WHERE ABS((DATEDIFF(DAY, [DateAccident], " + doa.ToString().Substring(0,10) + "))) < " + days.ToString() + ";";
        SqlCommand sqlCom = new SqlCommand(sqlText);
        table = Express.GetTable(sqlCom);
        return table;
    }
    catch (Exception eX)
    {
        throw new Exception("Case: DOACases(Date)" + Environment.NewLine + eX.Message);
    }
}

I do not know why

PS. Express.GetTable(sqlCom) simply creates a connection on the database and the necessary code to fill a DataTable using a DataReader and has worked hundreds of times, so I doubt the issue is there.

Upvotes: 0

Views: 191

Answers (2)

PJW
PJW

Reputation: 5417

Thanks to allo-man, using parameters worked.

The final code looked as follows -

public static DataTable DOACases(DateTime doa, int days)
    {
        try
        {
            DataTable table = new DataTable();
            string sqlText = "SELECT * " +
                             "FROM [Case] " +
                             "WHERE ABS((DATEDIFF(DAY, [DateAccident], @Date))) < @Days;";
            SqlCommand sqlCom = new SqlCommand(sqlText);
            sqlCom.Parameters.Add("@Date", SqlDbType.Date).Value = doa;
            sqlCom.Parameters.Add("@Days", SqlDbType.Int).Value = days;
            table = Express.GetTable(sqlCom);
            return table;
        }
        catch (Exception eX)
        {
            throw new Exception("Case: DOACases(Date)" + Environment.NewLine + eX.Message);
        }
    }

Upvotes: 1

Damith
Damith

Reputation: 63105

You better use parameters but here the problem is

'" + doa.ToString("yyyy-MM-dd" , CultureInfo.InvariantCulture) + "'

you need single quotes

Upvotes: 0

Related Questions