Valid date-time values in sql server. SqlDateTime vs DateTime

You cannot store every date in sql server. The valid range of dates is from 1/1/1753 (1-1-1753) to 12/31/9999 (31-12-9999). The range of the .NET DateTime type is far larger. So before storing a datetime in a sql server database you have to perform a check. This should be (and is) not to difficult in .NET. But as the documentation of SqlDateTime and other Google results are confusing hereby a quick summary.

The .NET framework has two types, DateTime and SqlDateTime The SqlDateTime type has implicit operators which convert a sql datetime into a regular DateTime. Thanks to this implicit type conversion you can mix both date types in an expression. At first sight the follwing code looks like a good check.

DateTime bla = DateTime.MinValue;  if ((bla >= SqlDateTime.MinValue) && (bla <= SqlDateTime.MaxValue))  {      // bla is a valid sql datetime  }  

To my initial surprise it throws a sql exception: "System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM." What happens is that before comparing the date the variable bla will be (due to the implicit operator) cast to SqlDateTime. Doing that it will hit the the sqlexception. The rule is that in an expression with two different types they are converted to the narrowest of the two. So what will work is explicitly cast the SqlDateTime to a DateTime. Like this

DateTime bla = DateTime.MinValue;  if ((bla >= (DateTime)SqlDateTime.MinValue) && (bla <= (DateTime) SqlDateTime.MaxValue))  {      // bla is a valid sql datetime  }

This behavior will not show up until the test meets an invalid sql date at runtime.The good thing is that this same kind of implicit conversion can prevent a compile.

 

The message is enigmatic until you start realizing that the implicit conversion of date leads to a different type for the result of the expression. A SqlBool instead of a .NET bool.

This function builds and runs well.

static bool isValidSqlDate(DateTime date)  {      return ((date >= (DateTime)SqlDateTime.MinValue) && (date <= (DateTime)SqlDateTime.MaxValue));  }  

Far easier than the many parsing and testing frenzy I found Googling on this.

Source Click Here.