Calculating Age in Years, Months and Days in SQL Server 2012

Rohatash Kumar

SQL Server 2012GetDate FunctionSQL Datepart FunctionCalculate Age in SQLSQL datediff function

7 Comments

Join the conversation! Your thoughts help the community grow.

Luan Castro Aleixo11y ago
this code works... http://www.itdeveloperzone.com/2011/06/date-difference-in-years-months-and.html
Eric Smith11y ago
your code ignores the age of an infant. If you need the correct age, down to the day - look at the solution in Mamadou Doumbia's comment.
Mamadou Doumbia12y ago
Here is my code, it also works fine:********************************************* CREATE FUNCTION fnCalculAge(@DOB Datetime) RETURNS nvarchar(50) AS BEGIN DECLARE @tempdate datetime, @years int,@months int, @days int SELECT @tempdate = @DOB SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE())- CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END SELECT @tempdate = DATEADD(YEAR,@years,@tempdate) SELECT @months = DATEDIFF(MONTH,@tempdate,GETDATE()) - CASE WHEN DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END SELECT @tempdate= DATEADD(MONTH,@months, @tempdate) SELECT @days = DATEDIFF(DAY,@tempdate,GETDATE()) DECLARE @Age nvarchar(60) Set @Age = CAST(@years as nvarchar(4)) + ' years ' + CAST(@months as nvarchar(2)) + ' months and ' + CAST( @days as nvarchar(2)) + ' days.' return @Age END Use like this "SELECT dbo.fnCalculAge ('DOB')--DOB in datetime format. Thanks.
mukul joshi12y ago
useful article..
Prasanth Navaraj13y ago
Good article, but not working for some case
Rohatash Kumar13y ago
Thanks Baji Sankar.
baji sankar13y ago
Good article,,,

In this article, I would like to show the difference between two dates in years, months and days in SQL Server. In this article, we take an existing date and the current date and using the "GetDate" function to find the years, months and days. The "DATEDIFF" and "Datepart" functions are used to determine years, months and days between two dates. So let's have a look at a practical example of how to calculate age in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

First of all, we will see the use of the "DATEDIFF" and "Datepart" functions.

The DATEDIFF Function

The SQL Server "DATEDIFF" Function is used to calculate the difference between two dates.

Syntax

The syntax of the "DATEDIFF" built-in date function is as follows:

DATEDIFF(Datepart, Startdate, Enddate)

Here, The "Datepart" parameter is the part of the datetime to calculate the difference of. The Datepart can be any of the following:

Ms - Milliseconds

Yy - Year

Qq - Quarter of the Year

Mm - Month

Dy - The Day of the Year

Dd - Day of the Month

Wk - Week

Dw - The Day of the Week

Hh - Hour

Mi - Minute

Ss - Second

DATEDIFF Example

Declare @dateofbirth datetime
Declare @currentdatetime datetime
Declare @years varchar(4)
set @dateofbirth = '1986-03-15' --Birthdate
set @currentdatetime = getdate() --Current Datetime
select @years = datediff(year,@dateofbirth,@currentdatetime)
select @years + ' years,' as years

Output

The Datepart Function

The SQL Server "Datepart" function returns a portion of a SQL Server Datetime field.

Syntax

The syntax of the "Datepart" built-in date function is as follows :

DATEPART ([Date part], [Datetime])

Here, the <Date part> parameter is the part of the datetime. Datetime is the name of a SQL Server Datetime field and portion is one of the following:

Ms - Milliseconds

Yy - Year

Qq - Quarter of the Year

Mm - Month

Dy - The Day of the Year

Dd - Day of the Month

Wk - Week

Dw - The Day of the Week

Hh - Hour

Mi - Minute

Ss - Second

DATEPART Example

Declare@dateofbirthdatetime
Declare@currentdatetimedatetime
Declare@daysvarchar(3)
set@dateofbirth='1986-03-15'--Birthdate
set@currentdatetime =getdate()--Current Datetime
select@days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)
select @days +' days' asDays

Output

Calculating Age in years, months and days

Here is an example to get the years, months and days between two dates.

Declare@dateofbirthdatetime
Declare@currentdatetimedatetime
Declare@yearsvarchar(40)
Declare@monthsvarchar(30)
Declare@daysvarchar(30)
set@dateofbirth='1986-03-15'--birthdate
set@currentdatetime =getdate()--current datetime
select@years=datediff(year,@dateofbirth,@currentdatetime)-- To find Years
select@months=datediff(month,@dateofbirth,@currentdatetime)-(datediff(year,@dateofbirth,@currentdatetime)*12)
-- To Find Months
select@days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)-- To Find Days
select@years +' years, ' +@months +' months, '+@days +' days' asYearMonthDay

Output