Rounding down figures in a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rounding down figures in a query

Author

Topic

Jerry_Newlove
Starting Member

3 Posts

Posted - 2002-02-07 : 07:42:28

Hi,

I need to round down a money value to two decimal places, at the moment I have 4! I have declared that field as 'MONEY' as well.

My query is below, balance needs to be 2 decimal places

SELECT 'C' AS Account_type, Current_account_no AS Account_no, Balance
FROM Current_Account UNION ALL SELECT 'D', Deposit_account_no, Balance
FROM Deposit_Account UNION ALL SELECT 'L', Loan_account_no, Amount
FROM Loan_Account

Thanks,

Jerry

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-07 : 07:55:46

have a look at the following link.....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11578

found using forum search on 'floor'

in your case....multiply your raw number by 100 to get it into 100's....apply the floor function....and then divide by 100 to get back to units with 2 decimal places.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-07 : 09:09:45

You could also use the CONVERT() function:

SELECT Convert(numeric(10,2), moneyColumn) FROM myTable

The numeric and decimal datatypes allow you to set a fixed decimal to any length you like. Books Online has more details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources