'datetime2' error when using entity framework in VS 2010 .net 4.0

Answer 1

Entity framework handles all the dates as a Datetime2, so, if you fields in the database are Datetime, this could be a problem. We had the same problem here, and from what we found, populating all the date fields and changing the datatype, are the most commom solutions

Answer 2

Whatever fits in a datetime will fit in a datetime2 data type, vice versa this is not the case, you can stick a date of January 1500 in a datetime2 data type but datetime only goes back to 1753, a datetime2 column can go back all the way to the year 1. I would check what the min date that you are passing in is and if your tables have datetime2 or datetime data type columns

Answer 3

Use that SQL script to convert all the columns from datetime to datetime2. It skips all the tables contains 'aspnet' for your convinience.

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;

It works for me!