Q

Returning values with more than two digits to the right of the decimal point

How do I write a WHERE clause that will return only values that have more than two digits to the right of the decimal point (ie. 27.543)? The data type is decimal, length 9, precision 13, scale 6. This is a typical situation when you want a dollar figure that is exactly two decimal place to the right of the decimal. I hope this is not too easy.

When inputting a number as a decimal, MS SQL Server pads the end of the number with 0s to equal the number of places...

declared in the scale. In other words, 27.543 is really 27.543000. To get around this, find the number of digits between the decimal point and the first zero.

Example:

 declare @tbl_decimals table( dec_column dec(13, 6) ) insert into @tbl_decimals values(123.1) insert into @tbl_decimals values(1234.12) insert into @tbl_decimals values(12345.123) insert into @tbl_decimals values(1234.1234) select * from @tbl_decimals WHERE len(left(right(dec_column, 6), charindex('0', right(dec_column, 6)) - 1)) > 2 /* The number "6" should be replaced with the number indicated as the scale dec(precision, scale)*/

 

For More Information

This was last published in March 2002

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close