# Round at specific decimal point

• 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!

• 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 ;`
• 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.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• 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.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• Didn't work, but thanks.

• 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;`

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.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• giszzmo wrote:

Didn't work, but thanks.

Which solution? And why?

What have you tried?

• This was going to go into a LOAD statement using QlikView (which uses SQL syntax) but just didn't work the way that I wanted it to so I had to put it into an Expression (which uses Excel syntax), so it came out something like =if(FRAC(column1 >.71, ceil(column1),floor(column1))).

• I wonder why people think that everything that uses SQL uses the same SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• giszzmo wrote:

This was going to go into a LOAD statement using QlikView (which uses SQL syntax)

But it doesn't use T-SQL. You've asked on a SQL Server community here, and the SQL dialect that SQL Server uses is T-SQL.

I have no idea what dialect QlikView SQL is based on; but I doubt it's T-SQL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

• declare @num decimal

set @num = 28.6 --28.7

select case when (@num * 10) % 10 >= 7

then

ceiling(@num)

else

floor(@num)

end

• david.wootton wrote:

declare @num decimal

`decimal` what? Where's your scale and precision?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

• (38,0) default

• david.wootton wrote:

(38,0) default

I think not. You should really test things before posting them here. Using a precision of 0 causes immediate rounding to the nearest whole number.

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.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• Phil Parkin wrote:

david.wootton wrote:

(38,0) default

I think not. You should really test things before posting them here. Using a precision of 0 causes immediate rounding to the nearest whole number.

And this is exactly why I questioned you in the first place, David. It's so important that you declare your Length, Scales, and Precisions. Especially as the default values differ on the context omitted (i.e. a variable vs a column) and will cause unexpected results if undeclared.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 15 posts - 1 through 15 (of 16 total)