Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 404
Techiesyam Avatar asked Aug 02 '13 11:08

Techiesyam


2 Answers

  • 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

like image 136
xanatos Avatar answered Oct 16 '22 22:10

xanatos


that is just rediculos, datetime2 is more exact, it has a bigger daterange and takes up the same number of bytes (8).

here is the code for mssql:

alter table tablename alter column colname datetime
like image 34
t-clausen.dk Avatar answered Oct 16 '22 21:10

t-clausen.dk