SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

Age calculation with SQL Server

There seem to be many different methods being suggested to calculate an age in SQLServer.  Some are quite complex but most are simply wrong.  This is by far the simplest and accurate method that I know.

 

Declare @Date1 datetime
Declare
@Date2 datetime


Select
@Date1 = '15Feb1971'
Select @Date2 = '08Dec2009'
select CASE
WHEN
dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)
END as Age

This even copes with the tricky situation of 29th feb,  although I cant say correctly as according to Wikipedia birthdays may be on the 28th of Feb or 1st March.

This post has been part of T-SQL Tuesday, hosted this month by Adam Machanic

Comments

Dew Drop – December 8, 2009 | Alvin Ashcraft's Morning Dew said:

Pingback from  Dew Drop – December 8, 2009 | Alvin Ashcraft's Morning Dew

# December 8, 2009 1:13 PM

Uri Dimant said:

How about

DECLARE

 @birthdate AS DATETIME,

 @eventdate AS DATETIME;

SET @birthdate = '20040229';

SET @eventdate = '20070227'; -- also test '20070227', '20070301'

SELECT

 (CAST(CONVERT(CHAR(8),@eventdate,112) AS INT)

  - CAST(CONVERT(CHAR(8),@birthdate,112) AS INT)) / 10000;

# December 9, 2009 8:53 AM

Twitter Trackbacks for Age calculation with SQL Server - SQL and the like [sqlblogcasts.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Age calculation with SQL Server - SQL and the like         [sqlblogcasts.com]        on Topsy.com

# December 9, 2009 1:47 PM

Dew Drop – December 9, 2009 | Alvin Ashcraft's Morning Dew said:

Pingback from  Dew Drop – December 9, 2009 | Alvin Ashcraft's Morning Dew

# December 9, 2009 2:06 PM