Solved

rounding, and truncating a number

Posted on 2009-04-09
3
535 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

16 Experts available now in Live!

Get 1:1 Help Now