“Out of range” error converting datetime2 to datet

Question

I have a table (table1) with StartDate, StartTime, EndTime, all datetime2 and an OverMidnight boolean which specifies if the EndDate will be different (+1) from the StartDate.

I need to move these to another table (table2) with the columns StartDate and an EndDate, both need to be the datetime type, and use StartDate with StartTime to get the new StartDate and StartDate, OverMidnight, and EndTime to get the EndDate.

No matter what I have tried it gives me an error:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

Answer 1

DATETIME and DATETIME2 have different acceptable date ranges.

DATETIME:

January 1, 1753, through December 31, 9999

DATETIME2:

0001-01-01 through 9999-12-31

I would check whether you have any dates in your original data out of the DATETIME range or whether your conversion code is creating dates outside the DATETIME range in error.