Solved

rounding, and truncating a number

Posted on 2009-04-09
3
537 Views
Last Modified: 2012-05-06
I have a SQL Server query that is returning my values with many values beyond the decimal point, when I only need it to display two numbers beyond the decimal point. For example,
they are returned as
33.3333333333 or 16.6666666666
(My SQL code is listed in the snippet box below)
-----------------------------------
Instead, I need to be able to do two things. (1) display two decimal numbers past the decimal,
33.33 or 16.66 without rounding. And secondly to be able to round the number beyond the decimal up to the next highest value, or to round it down to the next lowest number. Like
33.00 and 17.00
DECLARE @EmployeeCount int

DECLARE @inputID int

SET @inputID=2

SELECT @EmployeeCount=COUNT(DISTINCT(intUserID)) from  tableSendResponse as theTotal

 where SendID IS NOT NULL AND (SendID=@inputID)

PRINT @EmployeeCount
 

SELECT 

     convert(varchar(30), DATEADD(hh,DATEPART(hh, [ResponseDateTime])), 100) dates,

     ((CAST(COUNT(intUserID) AS FLOAT)/@EmployeeCount)*100) theCount

FROM

      tableSendResponse where ResponseDateTime IS NOT NULL

GROUP BY

  convert(varchar(30), DATEADD(hh, DATEPART(hh, [ResponseDateTime]),

   CAST(FLOOR(CAST([ResponseDateTime] AS FLOAT)) AS DATETIME)),100)

Open in new window

0
Comment
Question by:brgdotnet
3 Comments
 
LVL 12

Assisted Solution

by:udayakumarlm
udayakumarlm earned 120 total points
ID: 24113393
use
select ((CAST(COUNT(intUserID) AS FLOAT)/@EmployeeCount)*100 as numeric(8,2)) theCount
instead of
((CAST(COUNT(intUserID) AS FLOAT)/@EmployeeCount)*100) theCount
0
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 140 total points
ID: 24113418
Make use of the following:
STR ( float_expression [ , length [ , decimal ] ] )
float_expression
Is an expression of approximate numeric (float) data type with a decimal point.

length
Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

decimal
Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.

SELECT STR(33.3333333333, 5, 2);
33.33
SELECT STR(16.6666666666, 5, 2);
16.66
In above, 5 indicates total length of value to display. If you mention > 5, then padded with left space that extra length of characters specified. 2 means to truncate till 2 places after decimal/decimal places.

SELECT STR(FLOOR (33.3333333333), 2, 0)
33
SELECT STR(CEILING(16.6666666666),2,0)
17

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 140 total points
ID: 24116831
Use ROUND followed by CAST.

To truncate, specify a non-zero value for the third param (second param = # dec places); for example:

SELECT CAST(ROUND(16.6666666666, 2, 1) AS DECIMAL(9, 2))

To round to a whole number, specify 0 as the # of dec places:

SELECT CAST(ROUND(16.6666666666, 0) AS DECIMAL(9, 2))
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now