Solved

TSQL script to calculate age

Posted on 2009-04-16
Medium Priority
819 Views
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
Question by:Aaron Thorn

LVL 13

Accepted Solution

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

LVL 60

Assisted Solution

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

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

LVL 39

Assisted Solution

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())
``````
0

LVL 13

Assisted Solution

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

LVL 5

Assisted Solution

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
``````
0

Featured Post

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
Course of the Month15 days, 8 hours left to enroll