Need Query to identify numbers with more than 3 decimals. 2

[LannyB]

I have a table CURTRAN with a column PRVPMT. I am trying to identify any numbers in the PRVPMT column that have more than 2 decimal places.

 

[SQLSister]

use charindex to find the postion of the decimal point and subtract that from the len of the field to get the len to the right of decimal point.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Select *
From   CURTRAN
Where  PRVPMT - Convert(Numeric(20,2), PRVPMT) <> 0

Convert to 2 decimal places and subtract. If the result = 0, then you have 2 or less digits after the decimal place.

I would NOT trust these results if your data type for PRVPMT is float or real.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LannyB]

The PRVPMT column may contain .01 to 99999.9999. I need the query to identify any number that has more than 2 decimal places, regardless of the number of digits prior to the decimal point.

 

[gmmastros]

That's what my query should do you (unless I misunderstood you).

If my query does not generate the correct results, please show some sample data and expected results.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LannyB]

GMMASTROS
I got the following error when I ran the query you gave me: Invalid field name. ORA-00904: "Numeric" invalid identifier.

 

[gmmastros]

You got that error message because you are using an Oracle database and asked advice in a Microsoft SQL Server forum. You would have better luck asking your question here:

forum1177

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LannyB]

George,

OK, thanks!

Lanny

 

[ESquared]

Lots of non-SQL-Server questions in the forum lately. Curious.