How to calculate age based on 2 dates in SQL Server

June 2nd, 2013 | Posted by Nitesh Luharuka in SQL Server | Utility

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.

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

  • Madhu Nair

    Thank you very much Nitesh!!!!

    Your solution was of great help to me

Close
Please support the site
By clicking any of these buttons you help our site to get better