I need to get the Average, rounded to 2 decimal points, of a difference of two dates.

Using Entity Framework Core I used the following:

```
var average = await games
.Where(x => x.Finish.HasValue)
.AverageAsync(x => Math.Round((x.Finish.Value - x.Start).TotalDays, 2));
```

This seems to be running in memory.

How can I calculate the average on the database (SQL)?

The problem is not the `Average`

, but the time span calculation - currently EF Core (2.x) does not support generic SQL translation of time span calculations.

For SqlServer though you could use some of the DateDiff methods introduced in EF Core 2.1. All they return `int`

values, so you can't use directly `DateDiffDay`

to get the equivalent of `TimeSpan.TotalDays`

, but you could use `DateDiffHour / 24d`

or `DateDiffMinute / (24d * 60)`

etc. to simulate it.

For instance,

```
var average = await games
.Where(x => x.Finish.HasValue)
.AverageAsync(x => Math.Round(EF.Functions.DateDiffHour(x.Start, x.Finish.Value) / 24d, 2));
```

translates to

```
SELECT AVG(ROUND(DATEDIFF(HOUR, [x].[Start], [x].[Finish]) / 24.0E0, 2))
FROM [Game] AS [x]
WHERE [x].[Finish] IS NOT NULL
```

Licensed under: CC-BY-SA with attribution

Not affiliated with Stack Overflow