SqlDateTime overflow Exception in Select command
I use stored procedure to get paged list and this is my method:
using(SqlConnection conn = new SqlConnection(_connectionString)) {
using(SqlCommand cmd = new SqlCommand("[GetPagedSP]", conn)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Passing Parameters
**Update**
SqlParameter spCreationDate = new SqlParameter("@CreationDate_9", CreationDate);
spCreationDate.IsNullable = true;
cmd.Parameters.Add(spCreationDate);
// ........
//Finished Passing Parameters
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read()) {
//Get Values
}
conn.Close();
}
}
And this is my stored procedure command:
CREATE TABLE #PagingTemp (
[RowId] [bigint] IDENTITY(1,1) NOT NULL,
[RecordId] [bigint]
);
INSERT INTO [#PagingTemp] ([RecordId])
SELECT [CAR].[Id]
FROM [Article] AS [CAR] ;
SELECT [CAR].*
FROM [Collections].[Article] AS [CAR]
INNER JOIN [#PagingTemp] AS [PT] ON [CAR].[Id] = [PT].[RecordId]
WHERE [PT].[RowId] BETWEEN 1 AND 50;
When I run the query in SQL every thing is fine, But in .NET I have an exception on this line:
SqlDataReader dr = cmd.ExecuteReader();
and the exception is:
System.Data.SqlTypes.SqlTypeException was unhandled by user code
Message=SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source=System.Data
Update
The example of run Query in SQL: 
That's so weird I don't understand what happened?
I don't have any datetime value that be bigger than 12/31/9999 or less than 1/1/1753
I just have some Nullable datetime values with null value in database.
what do you think? where is the problem?
Assign any valid date to the variable CreationDate before passing it to execute the stored procedure
//Passing Parameters
CreationDate = DateTime.Now or any other date you want if you dont want to pass any date then assign DBNull.Value
**Update**
SqlParameter spCreationDate = new SqlParameter("@CreationDate_9", CreationDate);
spCreationDate.IsNullable = true;
cmd.Parameters.Add(spCreationDate);
Hope this will solve your problem.
IF possible, paste the SP here, it would be better to propose a fix. For now, you may consider following possibilities to try to fix it:
If select is causing issue then consider replacing NULL date with min date as following:
SELECT IsNULL(CreationDate,'1/1/1753') CreationDate, IsNULL(LastBidDate,'1/1/1753') LastBidDate from yourtable.
For reference, here is another answered question: error-sqldatetime-overflow-must-be between-1-1-1753-120000-am-and-12-31-999
As Gavin And Nikola Markovinović mentioned in comments in select command when you passed the DateTime type parameter need to check the parameter value is in correct range or not ( 1/1/1753 to 12/31/9999 )? I pass a Nullable DateTime parameter and I thought the value of that is null, also I never use this parameter in the query in this case, so the null value is correct and the use of parameter in command is not important, when I check again I found that the value of parameter is not null and is 1/1/0001 . another solution is use DATATIME2 type in SQL that support all range of DateTime which .Net supports.