Geeks With Blogs

Tim Huffam Dotting the I and crossing the T of I.T.

This error (System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.) will occur if you try to pass an uninitialised DateTime value into an SQL Server stored procedure from .NET code (in my case C# in an ASP.NET app).

To work around this you must pass in a value - null will not work. 

In my case I have a generated class that passes a System.DateTime variable into the System.Data.SqlClient.SqlCommand.ExecuteNonQuery() method.  So my only easy option was to make sure I pass in a valid DateTime value (I couldnt be shagged in altering my codesmith templates).

I decided to go with just setting the DateTime to a mimimum value (and just handling that in any code that had to look out unset dates) - however of you try to using System.DateTime.MinValue you'll be shit out of luck - as this equates to: 00:00:00.0000000, January 1, 0001 - which SQL Server does not support.

Instead set your DateTime variable to System.Data.SqlTypes.SqlDateTime.MinValue.Value - which equates to the lowest date value that a SQL Server datetime can store (01/01/1753 00:00).

Problem solved (for now) - this is the kind of hack that makes half-baked software possible ;-)

t

Update: 9/11 *** Special explanation note for those who didn't read or understand paragraph 3: 

This is a workaround (read: "half-baked" or "hack") based on two constraints:

  1. existing code that requires a valid System.DateTime value
  2. a lack of time, motivation or it's a non-production crappy piece of half-baked software that'll never be used by anyone else but yourself.  

However, if you are building a commercial/decent system that will be used and/or maintained by others, you *could* pass DBNull.Value to SQL Server (if you don't have a datetime value to pass) - however that would just be another hack (because if you dont have a value to pass, you'll potentially be overwriting an existing value with null) - so a proper, fully-baked solution, you should not be passing anything but instead change your db entry point to either provide an sproc or sql that only updates db columns as required (no shit Sherlock). 

 

Posted on Wednesday, November 8, 2006 8:32 PM C# .NET , SQL Server , ASP.NET , Half Baked | Back to top


Comments on this post: SQL Server datetime set blank or null = SQL Server error: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

# re: SQL Server datetime set blank or null = SQL Server error: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Requesting Gravatar...
Um, did you try DBNull.Value? null has never worked for any field... Don't hack things, figure them out correctly, cause some poor guy someday is going to have to support your "half baked" solution.
Left by Bruce Dunwiddie on Nov 09, 2006 3:10 AM

# re: SQL Server datetime set blank or null = SQL Server error: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must
Requesting Gravatar...
DBNull.Value is not working...
Left by Adam on Jul 09, 2007 11:00 PM

# re: SQL Server datetime set blank or null = SQL Server error: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must
Requesting Gravatar...
for setting the value of a parameter in C#:
dtActionDt = DBNull.Value; does not work
but
dtActionDt =System.Data.SqlTypes.SqlDateTime.MinValue.Value;
does work
However, in the Management Studio, this works fine:
update tbl_CaseNotes set ActionDate = null <some where clause>
It seems that null is a valid value for DateTime,you just can't get there from c#




Left by Walter Levine on Mar 14, 2008 5:20 AM

Your comment:
 (will show your gravatar)


Copyright © Tim Huffam | Powered by: GeeksWithBlogs.net