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.

One Response



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Support us!

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

×