AVG (DATE FORMAT) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

AVG (DATE FORMAT)

Author

Topic

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-12-01 : 06:22:04

HI,
I WANT TO AVG DATEFORMAT FIELDS.
EXAMPLE
DATE_
---------
12.30.2005
12.20.2005
12.10.2005

AVG(DATE_)=?
SELECT AVG(DATE_) FROM TABLE

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-01 : 06:29:32

not clear about what are you trying to achieve?

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 06:45:38

[code]
USE Northwind
GO

SELECT [Min] = MIN(OrderDate),
[Avg] = DATEADD(Day, AVG(DATEDIFF(Day, 0, OrderDate)), 0),
[Max] = MAX(OrderDate)
FROM dbo.Orders
GO

Min Avg Max
---------- ---------- ----------
1996-07-04 1997-08-08 1998-05-06
[/code]
The average looks about right, but I haven't checked it carefully!

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-01 : 06:46:09

You can't use avg function on datetime column. However, if you convert the date to days since a reference date, in this case i use 0 which is 1900-01-01 00:00:00 you can perform the average and then add the avg days back to the reference date.

select dateadd(day, avg(datediff(day, 0, DATE_)), 0) from TABLE

-----------------
[KH]

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-12-01 : 07:32:01

VERY THANKS.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources