Exact average age - 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)

Exact average age

Author

Topic

TurdSpatulaWarrior
Starting Member

36 Posts

Posted - 2004-06-11 : 13:45:05

Using the DATEDIFF function using years as the interval often doesn't yield accurate results. For example, a person with a birthday a week from today:

SELECT DATEDIFF(yyyy, '6/18/1980', GETDATE())

This person is still 23, however SQL rounds it up by default. Averaging ages using this would deffinitely yield innacurate results.

Here is the code that I am currently using to accomplish said task:

SELECT AVG(CAST(DATEDIFF(dd, DOB, GETDATE()) AS float) / 365.25) AS AverageAge FROM Customers

The .25 is because every fourth year is a leap year.

Has anyone come across a similar situation? If so, what code did you use to gather this information? I know that it could be done using more code, however I'm trying to keep this as simple and quick as possible, as the web based report this is part of is already chock full of fairly resource intensive queries.

Jay

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-06-11 : 14:00:17

I would stay away from float if I was you...if you need this to be exact you should use decimal instead...foat uses "approximation" (don't have a clue what that is) and it is not 100% accurate. Not sure if it will make a difference but this is my best bet:

SELECT CAST(DATEDIFF(dd, '6/18/1980', GETDATE()) AS decimal(18, 7)) / 365.25 AS AverageAge

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-11 : 14:37:13

Lumbago is right about float. It should never be used unless you have a scientific application that specifically needs it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources