Search code examples
sqldatetimedatetime2

Converting a table Colum from datetime2 to datetime

I have been assisgned with a starnge assigment where i need to convert 40 tables columns from datetime2 to datetime.

this is the datetime format what i am having in my database.2007-11-12 00:00:00

it contains more than 90,000 records

Please assist

Solution
    • You have to check if you have values < January 1, 1753 (because they aren't compatible with datetime). For example

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

    • You have to decide what to do with those 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'

    • Modify the type of your column

    ALTER TABLE MyTable ALTER COLUMN MyColumn DATETIME