?
Solved

TSQL script to calculate age

Posted on 2009-04-16
6
Medium Priority
?
819 Views
Last Modified: 2012-05-06
I need to calculate the age of my clients.  DOB is the day of birth as:
[DOB] [smalldatetime] NULL,

I would like a query that can display the age, and then the average age of all the clients.  Is that possible.
0
Comment
Question by:Aaron Thorn
6 Comments
 
LVL 13

Accepted Solution

by:
sm394 earned 600 total points
ID: 24158948
select datediff(year,DOB,getdate())
from yourtable
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 400 total points
ID: 24158961
select datediff(yy, dob, getdate())
from tablename

for the average:

select avg(datediff(yy, dob, getdate()))
from tablename
0
 
LVL 12

Assisted Solution

by:udaya kumar laligondla
udaya kumar laligondla earned 200 total points
ID: 24158975
For age
select datediff(YY,DOB,getdate())
from TableName

For average age
select sum(datediff(YY,DOB,getdate()))/Count(*)
from TableName
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 total points
ID: 24159010
Age is not calculated as simple as a datediff on year because 31-Dec-2008 to 1-Jan-2009 has a datediff of 1.

This function will give you the age as of a given date.  To calculate current age, pass in GETDATE() as the @AsOfDate.
if object_id('[dbo].[fn_GetAgeAsOf]') is not null
     drop function [dbo].[fn_GetAgeAsOf]
go
create function [dbo].[fn_GetAgeAsOf] (@BirthDate datetime, @AsOfDate datetime)
returns int
AS
begin
declare @Age   smallint
select @age=datediff(yy, @birthdate,@asofdate)
if day(@birthDate)>day(@AsOfDate)
  set @Age=@Age-1
return @age
end
go 
select [dbo].[fn_GetAgeAsOf](getdate()-365,getdate())

Open in new window

0
 
LVL 13

Assisted Solution

by:sm394
sm394 earned 600 total points
ID: 24159046
select avg(datediff(year,Dob,getdate())) from YourTable
0
 
LVL 5

Assisted Solution

by:fesnyng
fesnyng earned 400 total points
ID: 24162432
As noted by BrandonGalderisi, is not DATEDIFF in years.  Most people are not a 'year older' until their birthday.  Unusual custom, but the custom none the less.  

Modify the snippet below to replace @bd with your column name and add a  FROM yourtable.

Average can be had by simply encapsulating the CASE statement with AVG.
DECLARE @bd DATETIME, @dt DATETIME
SET @dt = getdate()
 
---------age = 39----------
SET @bd = 'July 20, 1969'
 
SELECT 
	(CASE WHEN DATEADD(year, DATEDIFF(year, @bd, @dt), @bd) > @dt 
		THEN DATEDIFF(year, @bd, @dt) -1 
		ELSE DATEDIFF(year, @bd, @dt) END ) as age
 
------age = 40--------
SET @bd = 'January 1, 1969'
 
SELECT 
	(CASE WHEN DATEADD(year, DATEDIFF(year, @bd, @dt), @bd) > @dt 
		THEN DATEDIFF(year, @bd, @dt) -1 
		ELSE DATEDIFF(year, @bd, @dt) END ) as age

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question