CODEWITHSUNDEEP
sqlsql-server
Pat
Pat

Reputation: 113

SQL - exact decimal value from averaging a range

I tried reading up on previously asked questions, and trying different solutions but I cannot seem to figure it out. I am still learning so any tips and guidance is greatly appreciated.

Here is my query 

select  AVG(DATEDIFF(DAY, xx, yy)) AS DayDiff

FROM database1.dbo.table1

where month(datecompleted) = month(dateadd(month,-1,current_timestamp))
       and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
       and ApprovalRequiredFrom = 'GRM'

Result sql gives me is 9, but when I average it in excel it is 9.18. I need sql to give me 2 decimal places. I tried using 'cast' but I cant figure out the correct syntax.

Thanks

Upvotes: 0

Views: 136

Answers (2)

EzLo
EzLo

Reputation: 14199

DATEDIFF function returns integer (INT) values. Average (AVG) returns the same data type as the expression that holds inside. Operations between integer values (in SQL Server) return integer values. However, operations between integer and other numeric values (like float or decimal) return the last one.

So if you convert the DATEDIFF to return decimal, the result will be decimal.

SELECT AVG(CONVERT(DECIMAL(8,2), DATEDIFF(DAY, xx, yy))) AS DayDiff

Following the same rule, a hard-coded value of 1.0 represents a decimal, so multiplying any integer value with 1.0 yields the same value but with decimal data type.

SELECT  AVG(DATEDIFF(DAY, xx, yy) * 1.0) AS DayDiff

As Jeroen mentioned, converting the argument will yield a decimal average, but with more than 2 decimals. You will have to convert the average result at the end.

SELECT  
    CONVERT(DECIMAL(8,2), 
        AVG(DATEDIFF(DAY, xx, yy) * 1.0)) AS DayDiff

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270421

SQL Server does decimal division, even for averages. I just multiply by 1.0 as a quick fix:

select  AVG(1.0 * DATEDIFF(DAY, xx, yy)) AS DayDiff

Upvotes: 4

Related Questions