SQL Query – Find numbers with more than two decimal places

by

As a database administrator one simple health check you can perform on the information in the database is that dollars are being saved correctly to two decimal places. Quite often these numbers are calculated automatically by another system and placed in a ‘Money’ type column – which has the capacity to store up to four decimal places. Any incorrect information in the database can lead to further calculation issues and reporting inaccuracies.

There are several ways to query an SQL database for numbers with more two decimal places, but in my experience the most straight forward is by adding the following WHERE condition to the SELECT query in SQL Server Management Studio, where Amount_Ex is the column you’re checking.

WHERE FLOOR(Amount_Ex*100)!=Amount_Ex*100

 

SQL-FindNumbersWithMoreThanTwoDecimalPlaces1

This will filter the SELECT query to only include data which has more than two decimal places, for example:

42473.7399
448.899
948.6999

 

Was this article helpful?

Yes No

Related Articles