Integer division does it perform rounding? 1

[1DMF]

Hi,

I have a table which has to have the union reversed enginered as it has calculated keys.

To do this I am using normal division but as it is against a filed declared as integer I understand SQL automatically performs integer division (which is what I want).

However, does it perform rounding and if so how do I tell it not to? (well I want it to always round down)

cheers,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[IanWaterman]

Does this help

Using ROUND to truncate
The following example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

SELECT ROUND(150.75, 0);
GO
SELECT ROUND(150.75, 0, 1);
GO
Here is the result set.

Copy
--------
151.00

--------
150.00

Ian

 

[bborissov]

When you divide two integers the result is always integer and decimals are truncated (no rounding is performed):

DECLARE @Test1 int, @Test2 int
SET @Test1 = 5
SET @Test2 = 3

SELECT @Test1/@Test2               AS IntResult,
       @Test1*1.0/@Test2           AS FloatResult,
       ROUND(@Test1/@Test2, 0)     AS RounInt,
       ROUND(@Test1*1.0/@Test2, 0) AS RounFloat,
       CEILING(@Test1/@Test2)      AS CeilingInt,
       CEILING(@Test1*1.0/@Test2)  AS CeilingFloat,
       FLOOR(@Test1/@Test2)        AS FloorInt,
       FLOOR(@Test1*1.0/@Test2)    AS FloorFloat

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[1DMF]

Thanks Borislav , just what I needed to hear

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts