Arithmetic overload when converting data type to datetime

[cathiec]

i connect to a database and execute a stored procedure to find rows with dates between the parameters start date and end date. i am using C# and SQL Server.
a date time selector is used to select the required dates. the error that i get is: Arithmetic overflow error converting expression to data type datetime.


SqlConnection conn = new SqlConnection("xxxxxx"
SqlDataAdapter da;
DataSet ds;
SqlParameter workParam;

da = new SqlDataAdapter("stpLOCPYDates", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

workParam = new SqlParameter("@pFromDate", System.Data.SqlDbType.DateTime);
workParam.Direction = ParameterDirection.Input;

workParam.Value = dtsStartDate.SelectedDate.Date;



da.SelectCommand.Parameters.Add(workParam);

workParam = new SqlParameter("@pToDate", System.Data.SqlDbType.DateTime);
workParam.Direction = ParameterDirection.Input;
workParam.Value = dtsEndDate.SelectedDate.Date;

da.SelectCommand.Parameters.Add(workParam);

ds = new DataSet();
da.Fill(ds, "dbo.SSRFACC"

dgSales.DataSource = ds;
dgSales.DataBind();

 

[obislavu]

I thin the problem is in :
workParam.Value = dtsStartDate.SelectedDate.Date;
Try to say:
workParam.Value = dtsStartDate.SelectedDate.Date.ToDateString();
I put here a piece of complete code that is working.
This function update a DateTime field using a stored procedure sp_.
See the CloseDate field (DateTime type in SQL database) in the stored procedure and in the code

public int SetDateClosed(System.Data.SqlClient.SqlConnection SQLConnection, string sExceptionID, DateTime vDateTime)
{
	int vReturn=0;
	SqlDataAdapter vSqlDataAdapter=new SqlDataAdapter();
	if (SQLConnection.State==System.Data.ConnectionState.Closed) SQLConnection.Open();
	vSqlDataAdapter.SelectCommand = new SqlCommand("sp_SetDateClosed",SQLConnection);
	vSqlDataAdapter.SelectCommand.CommandType =CommandType.StoredProcedure;
	SqlParameter vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
	vSqlParameter.Direction = ParameterDirection.ReturnValue;
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@Username", System.Environment.UserName);
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ItemExceptionID", sExceptionID);
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@DateClosed", vDateTime.ToShortDateString());
	DataSet vDataSet = new DataSet();
	vSqlDataAdapter.Fill(vDataSet);
	vReturn= (Int32) vSqlDataAdapter.SelectCommand.Parameters["@ReturnValue"].Value;
	return(vReturn);
}

CREATE PROCEDURE dbo.sp_SetDateClosed
(@Username varchar(50),@ItemExceptionID varchar(50), @DateClosed as varchar(50))
AS
SET NOCOUNT ON
DECLARE @Return int
//...
Set @TableName='_tEvent'
Set @WhereClause='WHERE ExceptionID=' + ...
Set @SetFields='SET [ClosedDate]=' + '''' + @DateClosed + '''' 
Set @Cmd='UPDATE ' + @TableName + ' ' + @SetFields + ' ' + @WhereClause
EXEC(@Cmd)
RETURN (1)
GO

-obislavu-