Round off to the nearest value [Answered]RSS

5 replies

Last post Nov 26, 2014 11:55 PM by wmec

• vignesht5

Member

38 Points

250 Posts

Round off to the nearest value

Nov 25, 2014 04:30 AM|vignesht5|LINK

Hi,

I'm using this query to round off the numbers and this round off the next value. Now I need to round off to the nearest value means if the value is 45.67 then the value should be 45..

`parsename('\$' + convert(varchar,convert(money,round(sum(Column1 * Column2),0)),1),2)`

Really appreciate any suggestions.

• gimimex

Contributor

5460 Points

1123 Posts

Re: Round off to the nearest value

Nov 25, 2014 06:35 AM|gimimex|LINK

Hi,

Try:

`round(sum(Column1 * Column2), 0, 1)`

Hope this helps.

• Philosophaie

Participant

953 Points

785 Posts

Re: Round off to the nearest value

Nov 25, 2014 06:48 AM|Philosophaie|LINK

• limno

All-Star

122336 Points

9734 Posts

Moderator

Re: Round off to the nearest value

Nov 25, 2014 08:40 AM|limno|LINK

I have collected a set of ways to do this:

```declare @val decimal(6,2)=45.67

select  @val

, parsename(cast(@val as varchar(20)),2)
,stuff(cast(@val as varchar(20)),charindex('.',cast(@val as varchar(20))),len(cast(@val as varchar(20))),'')
,floor(@val)
,cast(@val as int) myInt,
Replace(@val,Replace(ABS(@val) % 1,'0.','.'),'')

,Cast (N'<root><row>' + Replace(@val, '.', '</row><row>') + '</row></root>' AS XML).value('(/root/row)[1]', 'nvarchar(50)')

, Stuff(@val, Charindex(0X2E, Cast(Ltrim(@val) AS VARBINARY(50)), 1), len(@val), '')

,Cast(@val-ABS(@val) % 1 as Int)```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Member

72 Points

43 Posts

Re: Round off to the nearest value

Use below query for this

parsename('\$' + convert(varchar,convert(money,convert(int,round(sum(Column1 * Column2),0,0))),1),2)

example:-

select convert(int,round(45.50,0,0))

if value is 45.50 to 45.99 then this will return 46 and if value is 45.01 to 45.49 then this will return 45.

• wmec

Contributor

6354 Points

5578 Posts

Re: Round off to the nearest value

Nov 26, 2014 11:55 PM|wmec|LINK

Try also

```select parsename('\$'+cast(floor(45.67) as money),2)
```

Many Thanks & Best Regards,
HuaMin Chen