Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: find records with more than 3 decimal points in field

    I was trying to use charindex but that didnt work. Also what happens if i change the column to money data type? does it round or truncate?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do not use MONEY datatype

    what is it now?

    and please, what does "more than 3 decimal points" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    do not use MONEY datatype
    OK.....why not?
    I'll admit to having no serious opinion on this one way to the other, so give me your reasoning.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming he means three decimal places (obviously alter the precision to suitable value):
    Code:
    WHERE myDecimal != CAST(myDecimal AS DECIMAL(18, 3))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    why not use money type? and yes it was looking for > 2 decimal places the current data type is float.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mikezx10 View Post
    the current data type is float.
    Well you are sort of a bit stuffed there. Floats are stored in binary. What might appear to use only one decimal place is only because of the display rounding values. For example, the float representation of 0.1 is actually 0.100000000001 (give or take a decimal place or two).

    Floats are approximate data types - if you are worried about decimal places then you have used the wrong data type and should have used something like DECIMAL (DECIMAL is a fixed precision data type).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2003
    Posts
    233
    I dont know why they used float, but why would you not suggest money data type?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You'll have to ask Rudy - I think it is perfectly valid. It is only a wrapper for DECIMAL(19, 4) really. The only objection I could imagine Rudy would have is I think it is proprietary to SQL Server, although the convention of four decimal places for money is not.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    You'll have to ask Rudy
    i would like to research things a bit more, i'm sure i saw another article saying not to use MONEY

    plus, i think microsoft is deprecating it soon, if not already

    meanwhile, here's some more background information, a longish thread but definitely worth reading...

    http://www.dbforums.com/database-con...positions.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2003
    Posts
    233
    So if i do use money datatype it wont stop people from adding in values with > 2 decimal places any way so maybe i should just use decimal(18,2)

    Also I tried
    SELECT SUM(amt) - Sum(CAST(AMT AS DECIMAL(18, 2)))
    FROM INCOME

    And the result is 0.000231742858886719

    What I was thinking of doing is selecting all rows into a table called incomeBak and then doing an update on the income table setting Amt col = CAST(AMT AS DECIMAL(18, 2)),

    Is that a good way to go?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •