CODEWITHSUNDEEP
sqlsql-server
aSystemOverload
aSystemOverload

Reputation: 3074

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

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

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

Upvotes: 33

Views: 83155

Answers (4)

Daniel-son
Daniel-son

Reputation: 1

I made this and I hope it helps others in the future! SQL stinks for not making this easier.

Declare @Day as int;
Declare @Month as int;
Declare @Year as int;
Declare @Date as varchar(50);
SET @Day = (SELECT AVG(DAY(Column_name)) FROM Table_Name)
SET @Month = (SELECT AVG(MONTH(Column_name)) FROM Table_Name)
SET @Year = (SELECT AVG(YEAR(Column_name)) FROM Table_Name)
SET @Date =  CAST(@Day as varchar) +'-' + CAST(@Month as varchar) + '-' + CAST(@Year as varchar);
SELECT CONVERT(datetime2,@Date,103)

Upvotes: 0

inouttennis2314
inouttennis2314

Reputation: 91

For those of you getting a conversion error (can't convert date to int or float)... Here's a simple solution using datediff and 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.

Upvotes: 8

Francis P
Francis P

Reputation: 13655

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.

Upvotes: 53

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

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

Upvotes: 7

Related Questions