1,105,380 Community Members

ROUND Function Not Rounding Up

Member Avatar
Stuugie
Posting Shark
925 posts since Jun 2012
Reputation Points: 39 [?]
Q&As Helped to Solve: 40 [?]
Skill Endorsements: 4 [?]
 
0
 

Hi All,

I am wanting to round up my values at query time to 0 decimal places from 2 decimal places. I have

SELECT DISTINCT 
                      b.vSeries_Table_Number, b.vSeries_Number, b.vSeries_Geography, b.vSeries_Type, b.vSeries_Unit_Type, a.Variable_Date, ROUND(a.Variable_Value, 0) AS 'Value'

but the output still has the 2 decimal places which are 0s and pointless in a report. This should word but for some reason is not. Any ideas?

Member Avatar
JorgeM
IT Addict
6,414 posts since Dec 2011
Reputation Points: 581 [?]
Q&As Helped to Solve: 963 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
1
 
Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 

I'd try convert(int,a.Variable_Value) by definition the integer doesn't have decimal points, plus I'm suspecting that it's not rounding because the field is char and either includes spaces or something similar that can't be interpreted by the round (perhaps wrong decimal symbol? ) .

Member Avatar
Stuugie
Posting Shark
925 posts since Jun 2012
Reputation Points: 39 [?]
Q&As Helped to Solve: 40 [?]
Skill Endorsements: 4 [?]
 
0
 

Thanks to you both. ROUNDUP is not recognized but CONVERT worked exactly how I needed it to. Thanks Big-ee Big!

Question Answered as of 1 Year Ago by adam_k and JorgeM
Member Avatar
Stuugie
Posting Shark
925 posts since Jun 2012
Reputation Points: 39 [?]
Q&As Helped to Solve: 40 [?]
Skill Endorsements: 4 [?]
 
0
 

@adam K, the field type is Numeric(18,2).

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 

It was really late last night and I wasn't thinking straight, of course round was working, but it still returned 2 decimal points, nobody said that round would return an integer, only that it would change the value.
For clarification, there is no roundup in SQL. The command is ceiling for rounding up and floor to round down:
http://msdn.microsoft.com/en-us/library/ms189818.aspx
http://msdn.microsoft.com/en-us/library/ms178531.aspx

They both wouldn't work because they return the same data type they receive in.

Member Avatar
JorgeM
IT Addict
6,414 posts since Dec 2011
Reputation Points: 581 [?]
Q&As Helped to Solve: 963 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
0
 

For clarification, there is no roundup in SQL

It looks like roundup is a SQL 2012 function.

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article