June 2, 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.

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


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
  • Madhu Nair

    Thank you very much Nitesh!!!!

    Your solution was of great help to me

  • R Taylor

    Perfect – great help…

  • Orlando Ruiz

    Excelente, muchas gracias.

Support us!

If you like this site please help and make click on any of these buttons!

Powered by WordPress Popup