locked
Get decimal value in smallmoney RRS feed

  • Question

  • I need to find records with a smallmoney column that have something other than zero in the 3rd and 4th decimal places.  For example, I have a column named MiscDed that is smallmoney and I want to check the values in the 3rd and 4th position to the right of the decimal point and get back only those not equal to 00 returned.  I tried SELECT CAST(RIGHT(MiscDed,4) As char(4)) but that did not work.  Thanks.
    Thursday, April 21, 2011 4:15 PM

Answers

  • DECLARE @Sample TABLE
    (
    	MyVal SMALLMONEY NOT NULL
    )
    
    INSERT INTO @Sample 
    VALUES (1),
    	  (2.366),
    	  (5.6632),
    	  (9.22);
    	  
    SELECT MyVal, 
    	MyVal/100*100 AS Rounded 
    FROM @Sample	
    WHERE MyVal <> MyVal/100*100
    
    
    • Proposed as answer by Eric Isaacs Thursday, April 21, 2011 5:19 PM
    • Marked as answer by Ai-hua Qiu Friday, April 29, 2011 5:45 AM
    Thursday, April 21, 2011 4:31 PM

All replies

  • There is a very similar thread on another forum:

    tek-tips thread


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, April 21, 2011 4:20 PM
  • DECLARE @Sample TABLE
    (
    	MyVal SMALLMONEY NOT NULL
    )
    
    INSERT INTO @Sample 
    VALUES (1),
    	  (2.366),
    	  (5.6632),
    	  (9.22);
    	  
    SELECT MyVal, 
    	MyVal/100*100 AS Rounded 
    FROM @Sample	
    WHERE MyVal <> MyVal/100*100
    
    
    • Proposed as answer by Eric Isaacs Thursday, April 21, 2011 5:19 PM
    • Marked as answer by Ai-hua Qiu Friday, April 29, 2011 5:45 AM
    Thursday, April 21, 2011 4:31 PM
  • There is a very similar thread on another forum:

    tek-tips thread


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Yes but they are possible overflow issues
    Thursday, April 21, 2011 4:47 PM