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
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?
Works in both with postgresql and Oracle
SELECT ename, sal, round(((sal * .15 + comm) /12),2)
FROM emp where job = 'SALESMAN'
DECLARE @porcentaje FLOAT
SET @porcentaje = (CONVERT(DECIMAL,ABS(8700)) * 100) / CONVERT(DECIMAL,ABS(37020))
SELECT @porcentaje
CAST(QuantityLevel AS NUMERIC(18,2))
As with SQL Server 2012, you can use the built-in format function:
SELECT FORMAT(Minutes/60.0, 'N2')
(just for further readings...)
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 the denominator TotalContainers
, but I want my result to have up to 6 decimal places.
thus:
((m.TotalPackages * 1.000000) / m.TotalContainers) AS Packages,
try this : SELECT CAST(ROUND([Amount 1]/60,2) AS DECIMAL(10,2)) as TOTAL
Convert your number to a Numeric
or Decimal
.
Replace your query with the following.
Sql server
Select Convert(Numeric(38, 2), Minutes/60.0) from ....
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. The parameters for Numeric and Decimal (38, 2) and (65, 2) represent the maximum precision level and decimal places to use.
Declare @number float = 35.44987665;
Select round(@number,2)
you can use
select cast((630/60.0) as decimal(16,2))
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
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.
I find the STR function the cleanest means of accomplishing this.
SELECT STR(ceiling(123.415432875), 6, 2)
What ever 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 the we will get 15.48
select 1548/100
15.00000
select 1548/100.0
15.4800
0
Source: Stackoverflow.com