Rounding off to two decimal places in SQL
SqlRoundingSql Problem Overview
I need to convert minutes to hours, rounded off to two decimal places. I also need to display only up to two numbers after the decimal point. So if I have minutes as 650, then hours should be 10.83.
Here's what I have so far:
Select round(Minutes/60.0,2) from ....
But in this case, if my minutes is, say, 630 - hours is 10.5000000. But I want it as 10.50 only (after rounding). How do I achieve this?
Sql Solutions
Solution 1 - Sql
You could cast your result as numeric(x,2). Where x <= 38.
select
round(630/60.0,2),
cast(round(630/60.0,2) as numeric(36,2))
Returns
10.500000 10.50
Solution 2 - Sql
With SQL Server 2012, you can use the built-in format function:
SELECT FORMAT(Minutes/60.0, 'N2')
Solution 3 - Sql
You can use:
select cast((630/60.0) as decimal(16,2))
in SQL Server
Solution 4 - Sql
Declare @number float = 35.44987665;
Select round(@number,2)
Solution 5 - Sql
CAST(QuantityLevel AS NUMERIC(18,2))
Solution 6 - Sql
Convert your number to a Numeric or Decimal.
Replace your query with the following.
Select Convert(Numeric(38, 2), Minutes/60.0) from ....
Select Convert(Minutes/60.0, Decimal(65, 2)) from ....
The Cast function is a wrapper for the Convert function. Couple that with SQL being an interpreted language and the result is that even though the two functions produce the same results, there is slightly more going on behind the scenes in the Cast function.
Using the Convert function is a small saving, but small savings multiply. The parameters for Numeric and Decimal (38, 2) and (65, 2) represent the maximum precision level and decimal places to use.
Solution 7 - Sql
Following query is useful and simple-
declare @floatExchRate float;
set @floatExchRate=(select convert(decimal(10, 2), 0.2548712))
select @floatExchRate
Gives output as 0.25.
Solution 8 - Sql
DECLARE @porcentaje FLOAT
SET @porcentaje = (CONVERT(DECIMAL,ABS(8700)) * 100) / CONVERT(DECIMAL,ABS(37020))
SELECT @porcentaje
Solution 9 - Sql
Try this:
SELECT CAST(ROUND([Amount 1]/60,2) AS DECIMAL(10,2)) as TOTAL
Solution 10 - Sql
This works in both with PostgreSQL and Oracle:
SELECT ename, sal, round(((sal * .15 + comm) /12),2)
FROM emp where job = 'SALESMAN'
Solution 11 - Sql
Whatever you use in denomination should be in decimal. For example, 1548/100 will give 15.00.
If we replace 100 with 100.0 in our example then we will get 15.48
select 1548/100
15.00000
select 1548/100.0
15.4800
0
Solution 12 - Sql
As an add-on to the answers below, when using INT or non-decimal datatypes in your formulas, remember to multiply the value by 1 and the number of decimals you prefer.
I.e. - TotalPackages is an INT, and so is the denominator TotalContainers, but I want my result to have up to six decimal places.
Thus:
((m.TotalPackages * 1.000000) / m.TotalContainers) AS Packages,
Solution 13 - Sql
The following snippet might help you:
select SUBSTR(ENDDTTM,1, 9), extract(DAY FROM (ENDDTTM)), ENDDTTM, BEGINDTTM, (ENDDTTM - BEGINDTTM),substr(BEGINDTTM, 1,15), substr((ENDDTTM - BEGINDTTM), 12, 8),
round((substr((ENDDTTM - BEGINDTTM), 12, 2)* 3600 + substr((ENDDTTM - BEGINDTTM), 15, 2)*60 + substr((ENDDTTM - BEGINDTTM), 18, 2)),2) as seconds,
round((substr((ENDDTTM - BEGINDTTM), 12, 2)* 60 + substr((ENDDTTM - BEGINDTTM), 15, 2) + substr((ENDDTTM - BEGINDTTM), 18, 2)/60 ), 2)as minutes,
round((substr((ENDDTTM - BEGINDTTM), 12, 2) + substr((ENDDTTM - BEGINDTTM), 15, 2)/60 + substr((ENDDTTM - BEGINDTTM), 18, 2)/3600 ),2) as hours
Solution 14 - Sql
I find the STR function the cleanest means of accomplishing this.
SELECT STR(ceiling(123.415432875), 6, 2)
Solution 15 - Sql
To round up to x decimal places:
SET @Result = CEILING(@Value * POWER(10, @Decimals)) / POWER(10, @Decimals)
where @Value is the value of the item to be rounded, @Decimals is the number of decimal places, for example, two in this instance.
Solution 16 - Sql
This worked for me:
SELECT FORMAT(Minutes/60.0, '0.00')