How to calculate age in T-SQL with years, months, and days

tsql datediff
Barry picture Barry · Sep 11, 2008 · Viewed 141.1k times · Source

What would be the best way to calculate someone's age in years, months, and days in T-SQL (SQL Server 2000)?

The datediff function doesn't handle year boundaries well, plus getting the months and days separate will be a bear. I know I can do it on the client side relatively easily, but I'd like to have it done in my stored procedure.

Answer

Dane picture Dane · Sep 11, 2008

Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a case statement that decrements the result where appropriate.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

Related questions

How to convert number of minutes to hh:mm format in TSQL?

I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format. Duration has values like 60, 120,150 For example: 60 becomes 01:00 hours 120 becomes 02:00 hours 150 becomes 02:30 hours Also, this is how …

Read More
using DateDiff to find duration in minutes

I am trying to use Datediff to find out the duration between columnA and columnB. SELECT datediff (minute, stime, etime) from Exceptions2 where stime = [exceptions2].starttime and etime = [exceptions2].endtime This produces errors. Can anyone please help me with what …

Read More
how to get the number on months between two dates in sql server 2005

I have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since I also have the date the employee was engaged, I want the "months_In_Service" column to …

Read More