1

How to change data type of column in sql server if the column contain millions of rows?

I try this it should work.

 alter table employee
 alter column dob datetime

but I get an error.

That is - table contain huge amount of data or it is full you are not able to change data type.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Abhishek
  • 291
  • 1
  • 2
  • 12
  • From what to what? Obviously, if the column is nvarchar and contains all sorts of stuff, it won't be able to convert it to datetime - and why should it? – dyson Jan 19 '15 at 17:57
  • from smalldatetime to datetime – Abhishek Jan 19 '15 at 18:00
  • Malians = millions? :P Have you tried creating a new column, setting the data and then deleting the old column? You can finally rename the column, if necessary, as explained here: http://stackoverflow.com/questions/16296622/rename-column-sql-server-2008 – Andrew Jan 19 '15 at 18:06
  • Do you have a default value on the column? If so, you'd need to drop that first and then re-add it afterwards – dyson Jan 19 '15 at 18:07
  • 1
    Tables don't get "full" - maybe you should copy and paste the actual error message here. – Aaron Bertrand Jan 19 '15 at 18:19
  • 2
    ***WHAT*** error do you get? – marc_s Jan 19 '15 at 19:10

1 Answers1

1

Please take following steps:

  1. create new column with desired datatype and new name
  2. update values of that column with converted values from the old one
  3. remove the old column
  4. rename the new one to the old name

If you go this way, you can see, where the problem is:

  • step 1 - you have no rights?
  • step 2 - there is some problem with conversion
  • step 3 - I guess there is some foreign key or index which holds this column in place
cyberhubert
  • 203
  • 1
  • 9