SQL -四舍五入到小数点后两位

I need to convert minutes to hours, rounded off to 2 decimal places.I also need to display only up to 2 numbers after the decimal point. So if I have minutes as 650.Then hours should be 10.83

我需要把分钟转换成小时，四舍五入到小数点后两位。我还需要在小数点后显示最多2个数字。如果我有650分钟。那么时间应该是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?

但在这种情况下，如果我的时间是，比如说，630小时是10.5000000。但是我希望它是10.50(四舍五入后)。我该如何做到这一点?

12 个解决方案

#1

284

Could you not cast your result as numeric(x,2)? Where x <= 38

你不能把结果转换成数值(x,2)吗?x < = 38

select 
    round(630/60.0,2), 
    cast(round(630/60.0,2) as numeric(36,2))

Returns

10.500000   10.50

#2

As with SQL Server 2012, you can use the built-in format function:

与SQL Server 2012一样，您可以使用内置的格式函数:

SELECT FORMAT(Minutes/60.0, 'N2')

(just for further readings...)

(只是为了进一步阅读…)

#3

you can use

您可以使用

select cast((630/60.0) as  decimal(16,2))

#4

Declare @number float = 35.44987665;
Select round(@number,2)

#5

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.

给输出为0.25。

#6

DECLARE @porcentaje FLOAT

SET @porcentaje = (CONVERT(DECIMAL,ABS(8700)) * 100) / CONVERT(DECIMAL,ABS(37020))

SELECT @porcentaje

#7

Works in both with postgresql and Oracle

可以在postgresql和Oracle中使用

SELECT ename, sal, round(((sal * .15 + comm) /12),2) 
FROM emp where job = 'SALESMAN'

#8

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

#9

What ever you use in denomination should be in decimal, for example 1548/100 will give 15.00

你使用的任何面值都应该是十进制的，例如1548/100将给出15.00

If we replace 100 with 100.0 in our example the we will get 15.48

如果我们用100替换100，我们得到15。48

select 1548/100 
15.00000

select 1548/100.0
15.4800

0

#10

try this : SELECT CAST(ROUND([Amount 1]/60,2) AS DECIMAL(10,2)) as TOTAL

试试这个:选择CAST(ROUND([Amount 1]/60,2)作为DECIMAL(10,2)作为TOTAL

#11

Convert your number to a Numeric or Decimal.

把你的数字转换成数字或小数。

Replace your query with the following.

用以下内容替换您的查询。

Sql server

Sql server

Select Convert(Numeric(38, 2), Minutes/60.0) from ....

MySql:

MySql:

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.

Cast函数是转换函数的包装器。与SQL是一种解释语言的结合，结果是即使两个函数产生相同的结果，但是在Cast函数的幕后会有更多的发生。使用转换函数是一个小的节省，但是小的储蓄增加。

#12

-1

I find the STR function the cleanest means of accomplishing this.

我发现STR函数是最简洁的实现方法。

SELECT STR(ceiling(123.415432875), 6, 2)

#1

284