Joshua Java

Average of a datetime datatype field in SQL Server 2000

Posted on: June 14, 2008

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

  1. 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.
  2. Now before using the avg() function I must convert the datetime values into real datatype.
  3. After having the averay in real datatype values, I would convert it back to the datetime datatype.
  4. 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. 🙂


2 Responses to "Average of a datetime datatype field in SQL Server 2000"

[…] – Average of a datetime datatype field in SQL Server 2000 saved by angelicid2008-10-01 – schedule_attribute on GitHub saved by TrendyTots2008-09-14 – PHP […]

Thanks man..2 gud

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: