System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime

This exception occurs when you set a date, which is earlier than January 1, 1753 to a datetime parameter in your SQL expression. Now, what does this "January 1, 1753" represent? This is the minimum date value that a DATETIME data type can have. Setting any date earlier to this date in your SQL expression will result in a SQL datetime overflow. Also such arithmetic overflow exceptions could occur when we set to a datetime field, a date which is later than 31st December, 9999, which is the maximum date value that a DATETIME data type can have.

In order to avoid such SQL exceptions, it is always a good practice to validate your date to ensure that your date time is never beyond the min and max limits of SQL date time.

The following sample code will help you to validate your date:

VB.NET:
Dim dtSQLMinDate As DateTime = New DateTime(1753, 1, 1)
Dim dtSQLMaxDate As DateTime = New DateTime(9999, 12, 31)
Private Function ValidateMyDate(ByVal dtMyDate As DateTime) As Boolean
    If dtMyDate < dtSQLMinDate Then
        MsgBox("Enter a valid date which is later than Jan 1, 1753")
    End If
    If dtMyDate > dtSQLMaxDate Then
        MsgBox("Enter a valid date which is earlier than Dec 31, 9999")
    End If
End Function

C#.NET:
DateTime dtSQLMinDate = new DateTime(1753, 1, 1);
DateTime dtSQLMaxDate = new DateTime(9999, 12, 31);
private bool ValidateMyDate(DateTime dtMyDate)
{
    if (dtMyDate < dtSQLMinDate) {
        Interaction.MsgBox("Enter a valid date which is later than Jan 1, 1753");
    }
    
    if (dtMyDate > dtSQLMaxDate) {
        Interaction.MsgBox("Enter a valid date which is earlier than Dec 31, 9999");
    }
}

System.Data.EvaluateException: Cannot perform '=' operation on System.Int16 and System.String

This exception was thrown while filtering out rows from a DataTable with an incorrect filter expression. See the sample code shown below...

DataTable dt = GetDataTable();        
DataRow[] drs = dt.Select("ID='" +param + "'");

private DataTable GetDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("ID", typeof(Int16)));
    DataRow dr;
    for (int i = 0; i <= 100; i++)
    {
        dr = dt.NewRow();
        dr["ID"] = i;
        dt.Rows.Add(dr);            
    }
    return dt;
}

The GetDataTable() method just returns a sample DataTable with one column of Type Int16. The param in the filter expression is of string type. The expression works fine as long as the param string is a number, like 0, 1, 2... since ID is an Int16 column. The expression can be framed either as "ID = '2'" or "ID = 2". When param is an empty string or null, the above exception is thrown as these types of strings cannot be converted into a type that is equivalent to the comparing column (Int16).

So the next time when you use a filter expression to filter out rows from a DataTable ensure that you use the right Data Types.