如何在 SQL Server 中的日期列中查找平均值

How to find the average value in a column of dates in SQL Server

提问by aSystemOverload

I have a table of 5000 records with a date column.

我有一个带有日期列的 5000 条记录表。

How do I find the average of those dates. I've tried AVG(datefield), but it says Operand data type datetime is invalid for avg operator

我如何找到这些日期的平均值。我试过了AVG(datefield)，但它说Operand data type datetime is invalid for avg operator

回答by Francis P

If you want to average date only:

如果您只想平均日期：

SELECT CAST(AVG(CAST(datefield AS INT)) AS DATETIME) FROM dates;

And if you want to consider time:

如果你想考虑时间：

SELECT CAST(AVG(CAST(datefield AS FLOAT)) AS DATETIME) FROM dates;

See fiddleto test.

请参阅小提琴进行测试。

回答by LittleBobbyTables - Au Revtheitroad

CONVERT(DATETIME, AVG(CONVERT(FLOAT, datefield))) as [AverageDate] 

回答by inouttennis2314

For those of you getting a conversion error (can't convert date to int or float)... Here's a simple solution using datediffand dateadd

对于那些遇到转换错误的人（无法将日期转换为 int 或 float）...这是一个使用datediff和的简单解决方案dateadd

SELECT 
DATEADD(DAY, AVG(DATEDIFF(DAY, '1900-01-01', datefield)), '1900-01-01') AS [AverageDate]
FROM dates;

Note that the January 1st 1900 date is arbitrary, it just has to be the same in both spots.

请注意，1900 年 1 月 1 日的日期是任意的，两个地点的日期必须相同。