Solved

# sql 2014 query

Posted on 2014-04-30
363 Views
Last Modified: 2014-05-01
Hi experts- can anyone with access to sql 2014 run this query and see if the syntax is available.. 2012 gives error:
http://sqlfiddle.com/#!6/b1698/49
0
Question by:25112
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
7 Comments

LVL 16

Expert Comment

ID: 40031838
it says roundup.. i think there is a round function.. try this

select ROUND (111,40)
0

LVL 5

Author Comment

ID: 40031848
yes, that just rounds to specified # of digits.. but roundup and rounddown does more (away or toward)?

select ROUNDUP (111,40)
select ROUNDDOWN (111,40)

http://technet.microsoft.com/en-us/library/ee634948.aspx
http://technet.microsoft.com/en-us/library/ee634963.aspx
http://technet.microsoft.com/en-us/library/ee634402.aspx
0

LVL 8

Expert Comment

ID: 40031875
Try CEILING and FLOOR though they don't handle digits.
To round up to three digits, you may try e.g. CEILING (numeric_expression * 1000) / 1000
As "poor man's ceiling" you may use ROUND(numeric_expression + 0.4999999) with an appropriate number of nines. ROUND (numeric_expression + 0.5) will round one higher if you have an integer value to start with.
0

LVL 5

Author Comment

ID: 40031895
Surrano, with your example how would you convert
ROUNDUP(1.3,0.2)
with
ROUND or CEILING?
0

LVL 8

Expert Comment

ID: 40032330
It depends what you mean by ROUNDUP(1.3, 0.2) since ROUNDUP (in SQL Server 2012) has an integer for second parameter: 2 = 2 digits to the right of the decimal separator, -2 = 2 digits to the left. i.e.
ROUNDUP (123.456, 2) = 123.46
ROUNDUP (123.456, -2) = 200

If you mean round up to two fractional digits (i.e. to 1.30 but it doesn't make sense in this example):

ceiling (1.3*100)/100

If you mean round up to the nearest integer multiple of 0.2 (i.e. to 1.4):

ceiling (1.3/0.2)*0.2

Otherwise, please provide more examples with the desired result so that your interpretation of ROUNDUP is unambiguous. E.g. what should be the result of the following calls?

ROUNDUP (1234.5678, 0.2)
ROUNDUP (1234.5678, 0.3)
ROUNDUP (-1234.5678, 0.2)
ROUNDUP (-1234.5678, 0.3)
ROUNDUP (1234.5678, -0.2)
ROUNDUP (1234.5678, -0.3)
ROUNDUP (-1234.5678, -0.2)
ROUNDUP (-1234.5678, -0.3)
0

LVL 5

Author Comment

ID: 40032569
Surrano,
according to http://technet.microsoft.com/en-us/library/ee634948.aspx
ROUNDUP(PI(),4) = 3.1416
ROUNDUP(1.3,0.2) = 1.4
ROUNDUP(13.25,-1) =20

is ROUNDUP working in your instance?
0

LVL 10

Accepted Solution

PadawanDBA earned 500 total points
ID: 40034706
To briefly answer your question: roundup is not valid syntax in 2014.  The KB you posted a link to a DAX function, which if I am not mistaken, would be applicable to SSAS.  You would indeed need to get creative with round and/or ceiling.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a â€¦
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. Thatâ€™s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With companâ€¦
Simple Linear Regression
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll

#### 738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.