Financial Rounding.- Solved - 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 2005 Forums

Transact-SQL (2005)

Financial Rounding.- Solved

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-10 : 19:31:25

How do you do financial rounding in sql 2005?

(i.e.)
let's work with 2.555, and 2.5551

Financial rounding should round 2.555 to 2.55, but 2.5551 to 2.56

can you do this with the round function, or do I need to write a UDF for this?

Thanks

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-11 : 02:26:19

youll find a few examples here: http://www.sqlservercentral.com/Forums/Topic246556-8-1.aspx

Nathan Skerl

doco
Yak Posting Veteran

77 Posts

Posted - 2007-11-12 : 02:15:36

quote:

youll find a few examples here: http://www.sqlservercentral.com/Forums/Topic246556-8-1.aspx

O sure another site registration :-P

How about casting to Money or Decimal(10,2)?

doco

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-13 : 01:08:31

Doco, That will not work, please see example


declare @Val1 decimal(5,4)
declare @Val2 decimal(5,4)

set @Val1 = 2.555
set @Val2 = 2.5551

select cast(@Val1 as money),Cast(@val2 as money)
select cast(@Val1 as Decimal(10,2)),Cast(@val2 as Decimal(10,2))

Anyone have any solutions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 10:37:49

[code]DECLARE @Sample TABLE (Original DECIMAL(5, 4), ShouldBe DECIMAL(5, 4))

INSERT @Sample
SELECT 2.555, 2.55 UNION ALL
SELECT 2.5551, 2.56

SELECT Original,
ShouldBe,
CASE
WHEN Original - ROUND(Original, 2, 1) > 0.005 THEN ROUND(Original, 2, 1) + 0.01
ELSE ROUND(Original, 2, 1)
END AS Peso1,
ROUND(ROUND(Original + 0.0004, 3, 0) - 0.001, 2, 0) AS Peso2
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-13 : 17:59:04

Peso,

I was hoping there was a built in function for financial rounding, but your solution of

ROUND(ROUND(Original + 0.0004, 3, 0) - 0.001, 2, 0)

works very well.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 18:07:13

Thank you for the feedback.

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-13 : 18:19:51

Not that these are any practical help for your problem, but they're good flame wars around this subject.

Round to Even (aka Banker''s Rounding) - The...
http://www.sqlservercentral.com/Forums/Topic373178-8-1.aspx

Bankers Rounding
http://www.sqlservercentral.com/Forums/Topic370393-8-1.aspx

Good illustrations of "the battles are so bitter because the stakes are so small"

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources