Monday, January 11, 2010

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