locked
data type problem for price RRS feed

  • Question

  • User-804681621 posted

    what is the different between data type float, money, smallmoney, numeric

    if i want to sotre price with varing decimal place, what data type should I add to the column?

    or is there any problem if i create a varchar column to store the price?

    Sunday, February 28, 2016 10:54 AM

Answers

All replies

  • User-698989805 posted

    Hello pn_nq! Please refer to the following links:

    http://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql

    http://stackoverflow.com/questions/628637/best-data-type-for-currency-values

    varchar type is to store string or text data. If you use it for prices, you are unable to do the calculation that you can do with float or decimal. You can never add two strings or texts. Hope helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 28, 2016 6:18 PM
  • User-804681621 posted
    Why I sometime can't convert varchar but it is numEric to float?

    It will convert to 345e+007

    Why? How to fix?
    Monday, February 29, 2016 8:29 AM
  • User-219423983 posted

    Hi pn_nq,

    Why I sometime can't convert varchar but it is numEric to float?

    According to the above links provided by TechView and the links I posted in your former thread you could know that the best data type to store the money value is Decimal or Numeric. So, you’d better not use varchar to store the value or not convert it to float.

    You could have a look at the following example code.  The ‘xxe + xx’ is the scientific notation. About how to avoid the result, you could change the “float” to “decimal” or ‘numberic’ in your case.

    declare @va varchar(20) = '3450000000000000000000'
    select CONVERT(float,@va) --Result: 3.45E+19
    select CONVERT(Decimal(36,0),@va)  --Result: 34500000000000000000
    

    For the “345e+007”, I guess the value may be “3450000000”, when I use the convert(float,value) , it would return the ‘3450000000’, not the “345e+007” on my SQL Server Express of VS2015 Update1 . So, you’d better share a sample value and your convert query string to reproduce the errors.

    Best Regards,

    Weibo Zhang

    Tuesday, March 1, 2016 3:37 AM