Reader Level:
ARTICLE

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

Posted by Rohatash Kumar Articles | SQL Server January 01, 2013
In this article, I would like to show the difference between two dates in years, months and days in SQL Server.
  • 2
  • 0
  • 20132

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

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

DATEDIFF-Function-in-SQL-Server.jpg

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

DatePart-Function-in-SQL-Server.jpg

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

 

calculating-age-in-SQL-Server.jpg

Article Extensions
Contents added by Prasanth Navaraj on Sep 26, 2013
this article doesnot work if current month is less than actual date's month

Try this this results exact:

declare @dob datetime = '1985-10-15'
declare @currentDate datetime = getdate()
if(month(@dob)>MONTH(@currentDate))
    select datediff(year,@dob,@currentDate)-1 as ageinyears,12 - (month(@dob)-MONTH(@currentDate))as ageinMonths
else
    select datediff(year,@dob,@currentDate) as ageinyears,MONTH(@currentDate)-month(@dob) as ageinMonths


Result:

ageinyears    ageinMonths
27                            11
Contents added by baji sankar on Aug 09, 2013
This article helped to meet my requirement. I added some pepper to eliminate negative values.

DECLARE @dateofbirth datetime
DECLARE @currentdatetime DATETIME
DECLARE @years VARCHAR(40)
DECLARE @months VARCHAR(30)
DECLARE @days VARCHAR(30)

SET @dateofbirth='1984-12-20'--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

IF @days < 0 
BEGIN
IF @months > 0 
BEGIN
SET @months = CASE 
WHEN @days < 0
AND @months - 1 <= 0
THEN 12
WHEN @days < 0
THEN @months - 1
ELSE @months
END
END
ELSE
BEGIN
SET @months = 12 + @months
SET @years = @years - 1
PRINT @months
END

DECLARE @day INT
DECLARE @lastdayOfPrevMonth DATETIME

SELECT @day = datepart(d, @currentdatetime)

SELECT @lastdayOfPrevMonth = dateadd(d, - @day + @days, @currentdatetime)

SELECT @days = datepart(d, @lastdayOfPrevMonth)

PRINT @day
PRINT @lastdayOfPrevMonth
END

SELECT @years + ' years,   ' + @months + ' months,   ' + @days + ' days' asYearMonthDay



COMMENT USING

Trending up