Convert Colum table from datetime2 to datetime

Question

I was set up using starnge assigment where I need to convert 40 table columns from datetime2 to datetime.

this is the date and time format that I have in my database. 2007-11-12 00:00:00

contains over 90,000 entries

Please, help

Answer 1

• You should check if you have values ​​<January 1, 1753 (because they are not compatible with datetime ). for instance

SELECT * FROM MyTable WHERE MyColumn <'1753-01-01'

• You must decide what to do with these values, for example (here I change all the values ​​<1753-01-01 to 1753-01-01):

UPDATE MyTable SET MyColumn = '1753-01-01' WHERE MyColumn <'1753-01-01'

• Change Column Type

ALTER TABLE MyTable ALTER COLUMN MyColumn DATETIME

Answer 2

i.e. just rediculos, datetime2 is more accurate, it has a larger daterange and takes up as many bytes (8).

here is the code for mssql:

 alter table tablename alter column colname datetime