1

This is on SQL Server 2008.

I have several columns I want to convert from money and decimal to varchar, for example a column called item_amount.

  • How will these values be converted?

    Will it be the same as convert(varchar, item_amount)? Running a query like select item_amount, convert(varchar, item_amount) from <table> renders the columns identically, which is what I would expect and want.

  • I should be safe from possible truncation, correct?

    Assuming there are enough characters in the varchar column (which would be 39, since the max precision for a decimal column is 38 + 1 character for the decimal point). None of the numeric values are even close to 38 digits, most in the 3-5 range.

  • I've run this command successfully on a test table and want to make sure I'm not overlooking or forgetting something that's going to screw me: alter table <mytable> alter column item_amount varchar(39) default '0' (this is after droping the existing default ((0)) constraint).

Factor Mystic
  • 26,279
  • 16
  • 79
  • 95

1 Answers1

2

With regard to the way conversion is done, yes you are correct as long as the VARCHAR column you are placing it in has the right number of characters available you will be set to go.

With regards to your change of amount to varchar, you should be fine here as it will do the conversion.

I just have to note that it doesn't sound like a good idea to do this as you are no longer interacting with numbers for sorting, filtering, etc....but just a note.

Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
  • 1
    A side note that I would have named the constraint ... varchar(39) constraint df_tblname_colname default '0'... which keeps things clean and easy to drop as you have had to the (0). The other problem is that the decimal/money is EXPANDED to the full size, with 0-padding on the right. Not sure if that is what you are after – RichardTheKiwi Jan 13 '11 at 22:23
  • Your instincts aren't wrong, but these are import staging tables in basically a ETL process. Conversion scripts will do the convert() to proper numeric types when moving the data to production tables. – Factor Mystic Jan 13 '11 at 22:27
  • re "Conversion scripts will do the convert()" - ok, so not a problem then to keep the trailing 0's – RichardTheKiwi Jan 13 '11 at 22:28