remove decimal Place in SQL-CodePudding

Code :

days_between("Created Time","Locked Time")*24

Result database table :

Avg Response time (Hours)   
2.91                        
6.00                        
9.13                        
3.65                        
1.17                        
0.00                        
0.77                       
32.47

The table above is the result i get after the code but currenlty I want to remove all decimal place, So how can I do this. I tried to use cast and round () but is not working. Can someone help out. Thank you

Try code: days_between("Created Time","Locked Time")*24 , 0) AS INT ) AS Response_Time

CodePudding user response：

According to your sample data I guess:

with t(d) as ( 
    values (2.91), (6.00), (9.13), (3.65), (1.17), (0.00), (0.77), (32.47)
)
select d, cast(d/24 as decimal(5,2)) from t;

CodePudding user response：

I have a similar query running on MSSQL, and I can do it as:

SELECT TOP 10 a.SizeBytes AS originalValue
                ,COALESCE(a.SizeBytes, 0.00) / (1024 * 1024) AS defaultOperation
                ,CAST(COALESCE(a.SizeBytes, 0.00) / (1024 * 1024) AS INT) AS withCast
                ,FLOOR(COALESCE(a.SizeBytes, 0.00) / (1024 * 1024)) AS withFloor
                ,ROUND(COALESCE(a.SizeBytes, 0.00) / (1024 * 1024), 0) AS withRound
FROM filesUploaded a
WHERE a.SizeBytes > 0

Resulting in:

Note that I limited my query to the top 10, for speed generating this example, as my table contains millions of rows.

Also my starting value is integer, so I had to add the decimals in order to be able to see decimals. Probably you don't need to do that as the average should already have decimals, but in my case just dividing returned an integer result for the division without using the COALESCE before dividing.