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.
![Image1.jpg]()
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 :
![Image2.jpg]()
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 :
![Image3.jpg]()
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.