Working with dates and LINQ to SQL

Sunday, Apr 25, 2010 1 minute read Tags: linq-to-sql c# datetime sql
Hey, thanks for the interest in this post, but just letting you know that it is over 3 years old, so the content in here may not be accurate.

Something I’ve heard developers complain about on numerous occasion is that DateTime comparisons between SQL and .NET is a real pain. Often you need to do a comparison of the date against either a Min or Max value.

With raw .NET this is really quite easy, you can just use the DateTime struct and grab DateTime.MinValue or DateTime.MaxValue.

But if you’ve ever done this:

var res = from item in Collection 
          where item.CreatedDate != DateTime.MinValue 
          select item;

You’ll get the following exception thrown:

SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The problem is that DateTime.MinValue is actually 01/01/0001 12:00:00 AM.

So I’ve quite often seen hacks where a new date is being created which represent the minimum value of the SQL server, and all kinds of weird things, but that’s all redundant. The comparision value is built into the .NET framework.

All you need to use is System.Data.SqlTypes.SqlDateTime structure. This exposes two fields, MinValue and MaxValue. All you need to do is access the Value property of these and pass it into your LINQ statement. The date will be parsed correctly as a SQL valid date and you can do your comparisons!

So please, stop with any silly workaround for date comparisons with SQL and .NET :P