# Round at specific decimal point

• giszzmo

Mr or Mrs. 500

Points: 586

Hi, I am trying to round my numbers up to the nearest whole number if the decimal is equal to or greater than .7, for example 28.7 will be 29, or round down if it is below .7, for example, 28.6 will be 28.  I'm not sure If I need to truncate first and make a temp table or if there is a function that will do this.  If someone could point me in the right direction. Thanks!

• gilbert delarosa

Old Hand

Points: 392

this should do it. maybe make it into a Funtion

`DECLARE 	@TestNumber			decimal(4,1) = 28.7	,@PreferredDecimal	decimal(4,1)= 0.7;SELECT 	CASE WHEN @TestNumber - FLOOR(@TestNumber) >= @PreferredDecimal THEN CEILING(@TestNumber) ELSE FLOOR(@TestNumber) END ;SET @TestNumber = 28.6;SELECT 	CASE WHEN @TestNumber - FLOOR(@TestNumber) >= @PreferredDecimal THEN CEILING(@TestNumber) ELSE FLOOR(@TestNumber) END ;`
• Phil Parkin

SSC Guru

Points: 243762

Will this work?

`SELECT ROUND(@TestNumber - 0.2, 0);`

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

• Phil Parkin

SSC Guru

Points: 243762

Alternatively

`SELECT ROUND(@TestNumber + 0.3, 0, 1);`

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

• giszzmo

Mr or Mrs. 500

Points: 586

Didn't work, but thanks.

• Phil Parkin

SSC Guru

Points: 243762

giszzmo wrote:

Didn't work, but thanks.

Here's my test script.

`DECLARE @i DECIMAL(5, 3) = 0;DECLARE @Inc DECIMAL(3, 2) = 0.01;WHILE @i < 2BEGIN    PRINT CONCAT(@i, ', ', ROUND(@i + 0.3, 0, 1));    SET @i = @i + @Inc;END;`