Rounding - Is it possible to round down?

[Risk]

Hi

We have a financial system based on a SQL Server 2005 database and there is a requirement in this system to round numbers down after 2 decimal places. So for example:

2500.627 - would need to be rounded down to 2500.62

However, At the moment, SQL Server rounds this to 2500.63 which is obviously the logical way of doing this.

Is there any way in SQL Server that this can be achieved? I have tried different things with no success apart from casting the number as a char then stripping it down before casting it back to a number, however, doing this over a large number of rows slows down the process considerably.

Thanks in Advance.

Risk

 

[bborissov]

[COLOR=blue]declare[/color] @test [COLOR=blue]numeric[/color](10,5)
[COLOR=blue]SET[/color] @test = 2500.627 
[COLOR=blue]SELECT[/color] @test, [COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color](@test*100 [COLOR=blue]as[/color] [COLOR=blue]int[/color])/100.0 [COLOR=blue]as[/color] [COLOR=blue]numeric[/color](10,2))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Risk]

Many thanks Borislav, that works a teat.