SQLDataAdapter.Fill() throwing Arithmetic overflow error converting expression to data type datetime

Question

I have a custom method that handles calls to SQL which takes in a script and a list of SqlParamaters, executes the SQL and then fills a DataSet with any results via SqlDataAdapter. This has been in use for over 3 years on various projects without issue.

Here is a code snippet of how the custom method is called (the actual SQL script is stored in a file):

using (SQLClass _sql = new SQLClass(_Config.DatabaseInfo))
{
    FileInfo _file = new FileInfo("\\scripts\\GetEmployeeTable.sql");

    List<SqlParameter> _sqlParams = new List<SqlParameter>();
    // Convert DateTime to Decimal
    _sqlParams.Add(new SqlParameter("PERIODEND", DateToDecimal(periodEnd)));  

    _sql.ExecuteSqlCommandScript(_file, _sqlParams);
}

private Decimal DateToDecimal(DateTime myDate)
{
    Decimal _periodEndDecimal;
    String _periodEndString = myDate.ToString("yyyyMMdd");
    if (Decimal.TryParse(_periodEndString, out _periodEndDecimal))
        return _periodEndDecimal;

    return 0;
}

Here is the custom method:

ExecuteSqlCommandScript(FileInfo file, List<SqlParams> sqlParams)
{
    . . . (get _sqlScript by reading info from 'file')
    . . . (setup SqlConnection info)

    using (SqlConnection _conn = new SqlConnection(connectionString))
    {
        using (SqlCommand _cmd = new SqlCommand())
        {
            _cmd.CommandText = sqlScript;
            _cmd.Connection = _conn;
            _cmd.Connection.Open();

            // add SqlParameters to SQL command
            if (sqlParams != null)
            {
                _cmd.Parameters.AddRange(sqlParams.ToArray());
            }

            using (SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(_cmd))
            {
               try
               {
                    // Save Table info to Results object
                    DataSet _dataSet = new DataSet();
                    _sqlDataAdapter.Fill(_dataSet);    <!-- program falls into catch block here

                    SqlResult _result = new SqlResult();
                    _result.DataSet = _dataSet;
                    _result.TableCount = _dataSet.Tables.Count;

                    this.Results.Add(_result);
                }
            }
        }
    }
}

I am now updating an older project to use this method to query an employee table which stores dates as Decimal type (i.e., 12/25/2016 is stored in SQL as 21061225). This is a table from another company/product that we integrate with, so I CANNOT change this column to DateTime.

Here is the script I am executing:

SELECT ch.EMPLOYEE
FROM UPCHKH ch 
WHERE ch.PEREND = @PERIODEND

The older code used SqlDataReader to retrieve the results and this script would execute successfully. When I run this script using our custom procedure (using SqlDataAdapter), I get this error:

ERROR: Arithmetic overflow error converting expression to data type datetime.

I traced this call though SQL Server Profiler and this is the actual call to SQL:

exec sp_executesql N'
    SELECT ch.EMPLOYEE
        FROM UPCHKH ch 
        WHERE 
            ch.PEREND = @PERIODEND

',N'@PERIODEND decimal(8,0)',@PERIODEND=20161101

If I copy this exact script to a SQL window and run it, I get the expected results.

So, is SqlDataAdapter doing something funky to the SQL call or the results that are coming back from SQL? The custom method above is failing on the line identified. We are using .NET 4.5 and SQL 2014 in case that matters.

That error message will be generated when query is executed like this `ch.PEREND = 20161101` instead of `ch.PEREND = '20161101'`. Considering `ch.PEREND` is of `DATETIME` datatype — Pரதீப், Nov 07 '16 at 15:08
@Prdp `ch.PEREND` is not a `DateTime`, it is a `Decimal(9,0)` column. — BrianKE, Nov 07 '16 at 15:27
This error happens to occur precisely when the value `20161101` is converted to a `DATETIME` in SQL Server. The `SqlDataAdapter` code cannot produce this error. I know, I know -- according to what you see, there is no `DATETIME` anywhere. But when you have eliminated the impossible, whatever remains, however improbable, must be the truth. Check implausible stuff like executing in the wrong database, using the wrong connection string, tripping a trigger on a table somewhere or `_cmd` or `_parameters` not being what you expect (maybe there's more than one command in there). — Jeroen Mostert, Nov 08 '16 at 10:06
After much tinkering I found that if I simply pass `periodEnd` as `periodEnd.ToString("yyyyMMdd")`, I no longer get the error message. However, the value coming back is being converted to a `DateTime` value instead of remaining as a `Decimal` value. I have started a new question for this here: http://stackoverflow.com/questions/40488374/sqldataadapter-changing-value-type-returned-from-sql — BrianKE, Nov 08 '16 at 13:37
When setting up your sqlparameter, did you try specifying the type, and not leaving it a variant? I.E. _sqlParams.Add(new SqlParameter("PERIODEND", SqlDbType.Decimal, DateToDecimal(periodEnd))); — Kevin Cook, Jan 19 '17 at 22:15

SQLDataAdapter.Fill() throwing Arithmetic overflow error converting expression to data type datetime

0 Answers0

Linked