Rounding decimal places - SQL Server Forums

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

Transact-SQL (2000)

Rounding decimal places

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2005-09-19 : 08:03:32

I need to return a figure to round pennies.

Here's the relevant part of my SP:

@InvTax Money=0,
@Price Money=60.27
@TaxRate Money=0.175

SET @InvTax=@Price*@TaxRate

I want @InvTax to be 10.5400, not 10.5473
what do I need to do?

Thanks
Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 08:39:57

Cant you do this in your Presentation layer?

Try this

Declare @InvTax Money
Declare @Price Money
Declare @TaxRate Money
set @InvTax =0
set @Price =60.27
set @TaxRate =0.175
SET @InvTax=@Price*@TaxRate
select convert(decimal(12,2),@invTax)

Madhivanan

Failing to plan is Planning to fail

mark1504
Posting Yak Master

103 Posts

Posted - 2005-09-19 : 09:55:27

>>Cant you do this in your Presentation layer?

No. This proc (in the complete version) gathers data from tables and writes to the invoices tables.

>>select convert(decimal(12,2),@invTax)
Many thanks. That seems fine for the purpose.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-19 : 10:55:17

You could use the ROUND function:

select @InvTax=convert(decimal(10,2),round(@Price*@TaxRate,2))

Edit: fixed code

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 11:00:13

MVJ, did you mean this?

select @InvTax=convert(decimal(10,2),round(@Price*@TaxRate,2))

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 13:44:36

I agree; its not a Presentation Layer issue - the Tax Man will have a fit if his calculations are not done as per His Rules.

So that must make it a Business Rule, right? - in which case you need a User Defined Function called RoundAndPutPennyFractionsInSwissBanklAccount()

Note that ROUND() can take a third parameter to indicate whether "rounding" or "truncation" is to be performed. Also worth consider how Credit notes might behave - if you are storing them as Negative Amounts.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-20 : 01:11:37

>>RoundAndPutPennyFractionsInSwissBanklAccount()

Good Naming convention

Madhivanan

Failing to plan is Planning to fail

mark1504
Posting Yak Master

103 Posts

Posted - 2005-09-22 : 07:11:21

>> select @InvTax=convert(decimal(10,2),round(@Price*@TaxRate,2))

Isn't that belt and braces?

The UDF sounds fine in theory but from my experience the VAT man doesn't care much for fractions of pennies anyway (depending on your particular trade.) Alas, if he thought there was much to make from it he would change the rules.

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 08:54:33

"Isn't that belt and braces?"

Which bit?

Personally I would want the ROUND in there (I would then be sure of the behaviour of ROUND() [and even explicitly state ROUND or TRUNCATE if necessary]).

The CONVERT(decimal(10,2) does nothing for me

and I wouldn't want to rely on the default behaviour of the implicit conversion to money in:

select @InvTax=@Price*@TaxRate

if nothing else because [I think] that would be 4 d.p.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources