Been coding a bit with Microsofts MVC3 web framework lately and ran into this error:

{"The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.\r\nThe statement has been terminated."}

Apparently this is not an uncommon problem. There are quite a few forum threads about it but finding the reason or even better a solution was quite hard.

The reason:
The DateTime type in C# differs from the one that SQL uses.
C# can have null values and also accepts a wider span of dates, i.e: the date 0001/01/01 00:00 is ok in C#’s but throws the error above in SQL.

Solution
Solution one is to make sure you set all date variables to supported values (1753/1/1 to 9999/12/31) before you commit them to SQL.

The second option is to run this script on the Database after you rebuild it to replace the date type used on the columns to one that allows the same dates that C# does.

DECLARE @SQL AS NVARCHAR(1024)
DECLARE @TBL AS NVARCHAR(255)
DECLARE @COL AS NVARCHAR(255)
DECLARE @NUL AS BIT
DECLARE CUR CURSOR FAST_FORWARD FOR
    SELECT  t.name, c.name, c.is_nullable
    FROM    sys.tables AS t
    JOIN    sys.columns c ON t.object_id = c.object_id
    JOIN    information_schema.columns i ON i.TABLE_NAME = t.name AND i.COLUMN_NAME = c.name
    WHERE   i.data_type = 'datetime' and t.name not like '%aspnet%'

    ORDER BY t.name, c.name

OPEN CUR
FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = 'ALTER TABLE ' + @TBL + ' ALTER COLUMN ' + @COL + ' datetime2' + (CASE WHEN @NUL=1 THEN '' ELSE ' NOT' END) + ' NULL;'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL
END

CLOSE CUR;
DEALLOCATE CUR;

I used a mix of the two. I first ran the script and then went though my database tables to find out which columns in the database I was setting with “incorrect” dates and made sure I set those properly.