SqlDateTime overflow Exception in Select command

Question

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: enter image description here

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?

Check that your date/time column is returning a value and not null, otherwise you need to set your DateTime to DateTime?. — Gavin, Jul 30 '12 at 07:42
@Gavin As I said I have some `nullable` columns that returns null, but in object side my types are `DateTime?`, also before any mapping an exception throw in this line: `SqlDataReader dr = cmd.ExecuteReader();` — Saeid, Jul 30 '12 at 07:50
Out of interest, if you do have any DateTime values, can you post an example of one? — Gavin, Jul 30 '12 at 07:53
ah... Just out of interest, are you passing any date/time objects as parameters into your query? i.e. before con.Open(); If so, debug and make sure the date is not null, and is between the above dates — Gavin, Jul 30 '12 at 08:03
@Gavin Yes, I pass a nullable datetime parameter but the value of that is also null, I update the question see pass parameters part, Is this any fault to pass null value fore nullable datetime? — Saeid, Jul 30 '12 at 08:08
`CreationDate` is probably zero, not null but zero. Could you please verify that? — Nikola Markovinović, Jul 30 '12 at 08:10
I think the issue is, your query is trying to parse that null variable into a datetime type. I don't think SQL has the ability to handle that, so you would need to check if it is null or not first. i.e. `select * from Car where (Car.Date is null or Car.Date = @Variable)` — Gavin, Jul 30 '12 at 08:11
@Gavin I don't think so, I pass a where clause string parameter, The datetime parameter which I passed, just used if in where clause mention that. but the value of where clause is empty string. — Saeid, Jul 30 '12 at 08:20
@Gavin I just checked again, your right the passed date time parameter wasn't null, I'm sorry, and Thank you — Saeid, Jul 30 '12 at 08:28

Rukun Zaman · Answer 1 · 2012-07-30T08:35:47.583

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.

score 0 · Answer 2 · edited May 23 '17 at 12:13

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

Saeid · Accepted Answer · 2012-08-01T04:36:45.810

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.

SqlDateTime overflow Exception in Select command

3 Answers3