Thread: How to find the rows with records containing more than two numbers after the decimal

1. Registered User
Join Date
Feb 2013
Posts
46

Unanswered: How to find the rows with records containing more than two numbers after the decimal

How do I find the rows that have more than two decimal numbers after the decimal point for example 2.787686
I am trying :

select amount, LEN(AMOUNT) - CHARINDEX('.', amount) as DecimalCount from GL_REPORT
but is not working.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Oh my... You've opened up a can of worms that is SO nasty that I'm not quite sure where or how to help you with it! Which is your "right" answer (note that all of them are "right", I'm asking which one of these is "right" for you).
Code:
```DECLARE
@f       FLOAT         = 2
,  @i       INT           = 2
,  @m       MONEY         = 2
,  @n       NUMERIC(9, 1) = 2
,  @r       REAL          = 2
,  @x       INT           = 999

SELECT @f / @x, @i / @x, @m / @x, @n / @x, @r / @x```
Once we get that ironed out, then maybe I can start to help you!

-PatP

3. Registered User
Join Date
Feb 2013
Posts
46
My column is money and I have amounts showing with four digits after the decimal, the data got transferred this way and now I have to pull the amounts somehow and get rid of the extra two digits without using ROUND.

4. Registered User
Join Date
Apr 2012
Posts
213
Try something like this:

Code:
```select MyColumn * 100 % 1
-- ...```
Hope this helps.

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Originally Posted by M1N
My column is money and I have amounts showing with four digits after the decimal, the data got transferred this way and now I have to pull the amounts somehow and get rid of the extra two digits without using ROUND.
Why can't you use the Round() function?

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Without understanding precisely why you aren't allowed to use Round(), I can give you 200-300 answers. There is a very high probability that one or more of those answers will be correct, but it isn't certain.

I'm going to guess that the column stored as MONEY really is money and that you've got totals which have two decimal place accuracy that you need to match. Even if you use syntax that doesn't include a call to Round() like Imex provided, you are still effectively rounding under the covers as well as adding a new source of inconsistency to your data.

There's always a solution, but especially in the kind of problem that I've sketched out those answers aren't always obvious. There is no simple "one size fits all" solution for this family of problems... These need to be solved one at a time to meet the user requirements!

-PatP

7. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Just trying to think outside the box a little. What about some sort of truncation?

Code:
```  SELECT 1.23456 AS number INTO #test
SELECT left(number,charindex('.',number)+2) FROM #test
drop table #test```

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by clawlan
Just trying to think outside the box a little. What about some sort of truncation?
Good thought, but truncation is really just another form of rounding (and usually produces the same results). This isn't acceptable for formal financial reporting.

GAAP, JSOX, IFRS, etc. each have clear, well defined procedures to deal with this kind of issue. Whatever system or policy that M1N needs to work within almost certainly has one too. We just need to understand the requirements, then we can help with suggestions on how to implement the required policy.

-PatP

9. Registered User
Join Date
Feb 2013
Posts
46
I need to pull all the records that have 4 digits after the decimal this are the requirements.

10. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Originally Posted by M1N
I need to pull all the records that have 4 digits after the decimal this are the requirements.
something like this?

Code:
`WHERE len(substring(cast(number AS varchar),charindex('.',number)+1,len(number)-charindex('.',number))) = 4`

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by M1N
I need to pull all the records that have 4 digits after the decimal this are the requirements.
The MONEY datatype always has four digits, it is defined that way. Just pull all of the rows and you'll be fine.

This is exactly the point that I was trying to make from my first post. Without knowing exactly what M1N needed I was way off base assuming that something more complex was required.

-PatP

12. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Manytimes,
the most difficulty to answer an issue might be to define/understand the requirements exactly and undoubtly,
for all possible data and in all situations(in assumed environment including the datatype).

On this thread, you wrote first time
Originally Posted by M1N
How do I find the rows that have more than two decimal numbers after the decimal point for example 2.787686
I am trying :

select amount, LEN(AMOUNT) - CHARINDEX('.', amount) as DecimalCount from GL_REPORT
but is not working.
That first requirement("find the rows that have more than two decimal numbers after the decimal point for example 2.787686")
was alredy changed to
I need to pull all the records that have 4 digits after the decimal this are the requirements.
So, first my wants for you was that please clarify your requirements flawlessly, considering exceptional data.

You might think "have 4 digits after the decimal" were enough to describe your requirements.
But, without datatype(including number of digits and fractions),
some data having 4 digits after the decimal might be possible, like
'123.4567', '1234.0045', '123.4500', '123.0400', '12.3040', so on...

Are you required all of these data?
The MONEY datatype always has four digits, it is defined that way. Just pull all of the rows and you'll be fine.
Note: Ialic and Bold/Ialic were by me.

13. Registered User
Join Date
Feb 2013
Posts
46
Thanks fro the help, I didn't know that money datatype is defined as four digits.

14. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Tonkuma:
Originally Posted by M1N
My column is money and I have amounts showing with four digits after the decimal, the data got transferred this way and now I have to pull the amounts somehow and get rid of the extra two digits without using ROUND.
The MONEY datatype is a Sybase/Microsoft datatype which is stored as a 64 bit signed integer that is implicitly divided by 10000 which gives it four digits of scale.

Since the requirement is four digits to the right of the decimal place, every value for the MONEY datatype implicitly meets N1M's requirement.

Based on the original description, I thought that there were check totals with N digits of precision that needed to be matched so a simple Round() or truncate would not suffice. For those, detection is easy but correction (aka banker's rounding) is harder.

-PatP

15. Registered User
Join Date
Jan 2013
Posts
359

Can you get rid of MONEY?

I didn't know that money datatype is defined as four digits.
Did you also not know it does not do correct math? 1) It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. We were still doing display formatting in the database in the 1970's and this was how you got \$, commas and decimal signs in the output.

2) Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.

3) I violates many current GAPP and EU rules about precision

4) The MONEY data type has rounding errors.

Using more than one operation (multiplication or division) on money columns will produce severe rounding errors. A simple way to visualize money arithmetic is to place a ROUND() function calls after every operation. For example,

Amount = (Portion / total_amt) * gross_amt

can be rewritten using money arithmetic as:

Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)

Rounding to four decimal places might not seem an issue, until the numbers you are using are greater than 10,000.
BEGIN
DECLARE @gross_amt MONEY,
@total_amt MONEY,
@my_part MONEY,
@money_result MONEY,
@float_result FLOAT,
@all_floats FLOAT;

SET @gross_amt = 55294.72;
SET @total_amt = 7328.75;
SET @my_part = 1793.33;

SET @money_result = (@my_part / @total_amt) * @gross_amt;
SET @float_result = (@my_part / @total_amt) * @gross_amt;
SET @Retult3 = (CAST(@my_part AS FLOAT)
/ CAST( @total_amt AS FLOAT))
* CAST(FLOAT, @gross_amt AS FLOAT);

SELECT @money_result, @float_result, @all_floats;
END;

@money_result = 13525.09 -- incorrect
@float_result = 13525.0885 -- incorrect
@all_floats = 13530.5038673171 -- correct, with a -5.42 error

Posting Permissions

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