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

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
  1. Declare @dateofbirth datetime  
  2. Declare @currentdatetime datetime  
  3. Declare @years varchar(4)  
  4. set @dateofbirth = '1986-03-15' --Birthdate  
  5. set @currentdatetime  = getdate() --Current Datetime  
  6. select @years = datediff(year,@dateofbirth,@currentdatetime)  
  7. select @years   + ' years,' as years  
Output
 
DATEDIFF Function in SQL Server
 

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
  1. Declare@dateofbirthdatetime  
  2. Declare@currentdatetimedatetime  
  3. Declare@daysvarchar(3)  
  4. set@dateofbirth='1986-03-15'--Birthdate  
  5. set@currentdatetime =getdate()--Current Datetime  
  6. select@days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)  
  7. select @days   +' days' asDays  
Output
 
DatePart Function in SQL Server
 

Calculating Age in years, months and days

 
Here is an example to get the years, months and days between two dates.
  1. Declare@dateofbirthdatetime  
  2. Declare@currentdatetimedatetime  
  3. Declare@yearsvarchar(40)  
  4. Declare@monthsvarchar(30)  
  5. Declare@daysvarchar(30)  
  6. set@dateofbirth='1986-03-15'--birthdate  
  7. set@currentdatetime =getdate()--current datetime  
  8. select@years=datediff(year,@dateofbirth,@currentdatetime)-- To find Years  
  9. select@months=datediff(month,@dateofbirth,@currentdatetime)-(datediff(year,@dateofbirth,@currentdatetime)*12)  
  10. -- To Find Months  
  11. select@days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)-- To Find Days  
  12. select@years  +' years,   ' +@months +' months,   '+@days   +' days' asYearMonthDay   
Output
 
calculating age in SQL Server


Similar Articles