Passing NULL values to SQL Server, SQLDateTime and DateTIme

DateTime.MinValue indicates a NULL value in .NET. When you tried to insert the same to SQL Server you will come up with an over flow error saying only allowed values are in between the range 1/1/1753 to 12/31/9999. But in .NET the DateTime type can accept the date time values in between 0:00 1/1/000 (MinValue) and 23:59 12/31/9999 (MaxValue). That is why we are getting the error “SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM”.

The work around would be like this : if you need to pass NULL value to a datetime field from the .NET code behind pass the DateTime.Min value to the variable, and then at the time of assigning the same to SQL parameter check for that DateTime.Min value and assign the DBNull.Value when inserting.


if (strStartDate.Contains("1/1/0001 12:00:00 AM"))
 {
 comm.Parameters.Add(new SqlParameter("@startDate", SqlDbType.VarChar)).Value = DBNull.Value;
 }
 else
 {
 comm.Parameters.AddWithValue("@startDate", Employee.StartDate);
 }

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: