Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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

like image 597
aSystemOverload Avatar asked Oct 23 '12 13:10

aSystemOverload


People also ask

How do you find the average value of a column in SQL?

We have to pass the column name as a parameter. The avg() function has the following syntax: SELECT AVG( column_name ) FROM table_name; The avg() function can be used with the SELECT query for retrieving data from a table.

How do you calculate average in SQL Server?

AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error.

How do I find the average of multiple columns in SQL?

In PostgreSQL, to get the average of multiple (2 to 8) columns in one row just define a set of seven functions called average(). Will produce the average of the non-null columns.

What is the use of AVG () function in SQL?

SQL AVG function is used to find out the average of a field in various records. You can take average of various records set using GROUP BY clause. Following example will take average all the records related to a single person and you will have average typed pages by every person.


1 Answers

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.

like image 61
Francis P Avatar answered Oct 01 '22 23:10

Francis P