CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

how to get rid off decimals from sql results

I am calculating total hours/minutes but i would like to get rid off the decimals and only show something like this 2.00 hours or 2.5 hours etc. I am getting now something like this: 2.000000 and want only to limit to 2 decimals only.

 select DATEDIFF(minute, Min(FullDatetime), Max(FullDatetime)) / 60.0 as hours
  from  myTable
  where userid = 123

Upvotes: 0

Views: 446

Answers (5)

A_Sk
A_Sk

Reputation: 4630

try use

 cast('values' as decimal(18,2))   --2 decimal place.

 select Cast((DATEDIFF(minute, Min(FullDatetime), Max(FullDatetime)) / 60.0 as hours)as decimal(18,2))
  from  myTable
  where userid = 123

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You could use STR or CONVERT function:

DECLARE @v NUMERIC(19, 6)
SET     @v = 2.189189

SELECT STR(@v, 19, 2) AS Method1_Result_VARCHAR, CONVERT(NUMERIC(15, 2), @v) AS Method2_Result_NUMERIC
/*
Method1_Result_VARCHAR Method2_Result_NUMERIC
---------------------- ----------------------
               2.19    2.19
*/

Note: First argument of STR function has float type and this means that 1) SQL Server will convert this argument from numeric to float and 2) method 1 uses a non-deterministic expression.

Upvotes: 0

tsukanomon
tsukanomon

Reputation: 1270

just use ROUND function such as : SELECT ROUND(columnName,decimals) from table

Upvotes: 0

RobP
RobP

Reputation: 9522

You can do it by rounding but the easiest is to format for output using FORMAT(). 

 select FORMAT(DATEDIFF(minute, Min(FullDatetime), Max(FullDatetime)) / 60.0, 'N2') as hours
  from  myTable
  where userid = 123

Helpful original documentation: here

Upvotes: 2

Kalel Wade
Kalel Wade

Reputation: 7782

There are a few options out there. I prefer to use the following when no rounding is needed

FORMAT(value, 'N2')

SQL - Rounding off to 2 decimal places

how to get 2 digits after decimal point in tsql?

Upvotes: 0

Related Questions