I am doing this
SELECT * INTO table1 FROM Table0
I get an arithmetic overflow on a datetime column (error converting to smalldatetime), however the destinations schema that got created has a datetime column and not a smalldatetime column..
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
It sounds to me like you have an implicit conversion to smalldatetime, probably in a computed column. If you look at the Estimated Execution Plan, you'll probably see Compute Scalar operations in there. Hit F4 to see the properties, and you should be able to see the calculations that are being done, including any implicit conversions that are being used.
This kind of problem is always indicative of corruption and can easily be debugged with
DBCC CHECKTABLE(<tablename>) WITH DATA_PURITY;
Causes DBCC CHECKTABLE to check the table for column values that are not valid or out-of-range. For example, DBCC CHECKTABLE detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.