Can we modify the data type of a fields once data is entered

mkpjsr

Hi all,
can we modify the data type of a field without deleting the records from the table.
I tried to do so but its giving an error. Whats the solution.

matttrax

It depends on the type.

Usually you will need to create a temp field, copy the data into it, change the original field data type, then copy the data back (converting as necessary). Then delete the temp field.

DenSter

Think about that one. If you have a text field that contains a value 'test', could you change that to a numeric data type? If you have a code field with a length of 20, with a value 'VERYLONGCODEFIELD', could you change the datatype to Code10?

You can only change the data type when existing values are compatible. So you can change Text10 to Text20, because all values fit into the target data type. It doesn't work the other way around.

Which brings us to why you want to change. If you have existing data in the field, that must mean something, so you can't just go in and delete a whole bunch of values. You have to come up with a plan to convert values into the new data type. Don't try to just hack your way around and see where it ends. Think about it, write it up and design a solution that works.

mkpjsr

DenSter wrote:

Think about that one. If you have a text field that contains a value 'test', could you change that to a numeric data type? If you have a code field with a length of 20, with a value 'VERYLONGCODEFIELD', could you change the datatype to Code10?

You can only change the data type when existing values are compatible. So you can change Text10 to Text20, because all values fit into the target data type. It doesn't work the other way around.

Which brings us to why you want to change. If you have existing data in the field, that must mean something, so you can't just go in and delete a whole bunch of values. You have to come up with a plan to convert values into the new data type. Don't try to just hack your way around and see where it ends. Think about it, write it up and design a solution that works.

Actually there is a table with some integer type fields and now i want to convert it to decimal but it not allowing me to do so. I think there should not be any compatibility issue when we convert from Integer to Decimal.

kine

Because these types are different (you can change only size of text from smaller to bigger, you cannot change the type) you need to export the data from this field to some temporary field, clear the original field, change the datatype and move the data from temp. field back to the retyped field...

DenSter

mkpjsr wrote:

I think there should not be any compatibility issue when we convert from Integer to Decimal.

Integers and decimals are definately not compatible data types by default. You could make a case that from integer to decimal it should just always work, and from decimal to integer the system could at least check the data before causing an error. Unfortunately, it doesn't work like that, you're going to have to work around that.