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)，但它说AVG操作符的操作数数据类型datetime无效
2 个解决方案
#1
20
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 fiddle to test.
看到小提琴来测试。
#2
4
CONVERT(DATETIME, AVG(CONVERT(FLOAT, datefield))) as [AverageDate]
#1
20
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 fiddle to test.
看到小提琴来测试。
#2
4
CONVERT(DATETIME, AVG(CONVERT(FLOAT, datefield))) as [AverageDate]