Average of a datetime datatype field in SQL Server 2000
Posted June 14, 2008on:
In SQL Server 2000 you can not use the AVG() function on a datetime datatype field. So how do you get the average for a datetime datatype field?
This is the nasty way to do it:
convert(varchar, convert(datetime, avg(convert(real, end_time - start_time))), 108 ) avg_call_length
- In the example I subtracted two fields: start_time and end_time to get the length of how long user is using the system. You probably don’t need these part. If you don’t need it, you simply just put a datetime field in there instead.
- Now before using the avg() function I must convert the datetime values into real datatype.
- After having the averay in real datatype values, I would convert it back to the datetime datatype.
- You’re done upto this part if you’re happy with the datetime datatype. Unfortunately, the application require a time format to be displayed instead. SQL Server 2000 also don’t have the time datatype. So to get a time format I must convert it to a varchar datatype and then use the 108 type to get the time format values.
If you think you’ve got a better way to do this, please feel free to share with me. 🙂