Nitesh Kejriwal

02/06/2013 by Nitesh

How to calculate age based on 2 dates in SQL Server

Dear Friends,

Often working with SQL server, we need to calculate the difference between 2 dates. We can get this done easily by using the DATEDIFF() function. Sometimes, we also need to calculate the age of a person on a specific date in SQL Server. SQL Server does not provide a direct function to do this. Today, we will resolve this issue. Let us take an example –

Case Study:

We have a column in a table named DateOfBirth datetime storing the Anniversary Date of a user.  Lets assume it has the below data.
table-data

Now, let us use the DATEDIFF() to get the difference between dates in number of years. To do this, we will use the below query-

Select MemberId, DateOfBirth, DATEDIFF(YY,DateOfBirth,GETDATE()) AS NumberOfYears FROM Table1

We get the below output.
table-data-output-1
If you notice this output, you will find that the members having DOB as 9/12/2005 are treated as 8 years old but they are actually 7 years and some months as on this date.

Solution:

Run the below query and notice the output below the query –

Select MemberId, DateOfBirth, DATEDIFF(YY,DateOfBirth,GETDATE()) AS NumberOfYears,(CONVERT(int, CONVERT(varchar, GETDATE(), 112)) - CONVERT(int, CONVERT(varchar, DateOfBirth, 112)))/10000 AS RevisedNumberOfYears  FROM Table1

The output is as below-
Final Output
If you see the screenshot above, you will find that the RevisedNumberOfYears column displays the correct age of a member on the given date.

I hope this post helps you. Please let me know your thoughts via comments.

#SQL Server