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
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
This helped me, but I had to modify it by adding another condition:
…AND FLOOR(Amount_Ex*10)!=Amount_Ex*10
otherwise it was also finding shorter than 2…
(SQL Server 2014 sp2)
Thank you. Simple solution for which I thought would take lot of time for me to figure out.
Thanks, this definitely helped me today. Saved me a lot of time.
Thank you! This has saved me so much time and work!