ROUND Function Not Rounding Up

[Stuugie]

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?

[JorgeM]

Try the roundup function instead. http://msdn.microsoft.com/en-us/library/ee634948.aspx

[adam_k]

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? ) .

[Stuugie]

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

[Stuugie]

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

[adam_k]

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.

[JorgeM]

For clarification, there is no roundup in SQL

It looks like roundup is a SQL 2012 function.