How to calculate age in TSQL with Years, Months, and Days

Question

What would be the best way to calculate someone's age in years, months, and days in TSQL (SQLServer 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 sproc.

Answer 1

Are you trying to calculate the total days/months/years of an age? do you have a starting date? Or are you trying to dissect it (ex: 24 years, 1 month, 29 days)?

If you have a start date that you're working with, datediff will output the total days/months/years with the following commands:

Select DateDiff(d,'1984-07-12','2008-09-11')

Select DateDiff(m,'1984-07-12','2008-09-11')

Select DateDiff(yyyy,'1984-07-12','2008-09-11')

with the respective outputs being (8827/290/24).

Now, if you wanted to do the dissection method, you'd have to subtract the number of years in days (days - 365*years), and then do further math on that to get the months, etc.

Answer 2

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()) 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

Answer 3

Here is a (slightly) simpler version:

CREATE PROCEDURE dbo.CalculateAge 
    @dayOfBirth datetime
AS

DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int

SELECT @today = GETDATE()

SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)

SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)

SELECT @months = MONTH(@today - @thisYearBirthDay) - 1

SELECT @days = DAY(@today - @thisYearBirthDay) - 1

SELECT @years, @months, @days
GO

Answer 4

thx Leonardo for yours script, you solve my problem. great job

Answer 5

The same sort of thing as a function.

create function [dbo].[Age](@dayOfBirth datetime, @today datetime)
   RETURNS varchar(100)
AS

Begin
DECLARE @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int

set @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)
set @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)
set @months = MONTH(@today - @thisYearBirthDay) - 1
set @days = DAY(@today - @thisYearBirthDay) - 1

return cast(@years as varchar(2)) + ' years,' + cast(@months as varchar(2)) + ' months,' + cast(@days as varchar(3)) + ' days'
end

Answer 6

create procedure getDatedifference

( @startdate datetime, @enddate datetime ) as begin

declare @monthToShow int declare @dayToShow int

--set @startdate='01/21/1934' --set @enddate=getdate()

if(DAY(@startdate) > DAY(@enddate)) begin set @dayToShow=0

if (month(@startdate) > month(@enddate)) begin set @monthToShow= (12-month(@startdate)+ month(@enddate)-1) end else if (month(@startdate) < month(@enddate)) begin set @monthToShow= ((month(@enddate)-month(@startdate))-1) end else begin set @monthToShow= 11 end -- set @monthToShow= convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))-1 if(@monthToShow<0) begin set @monthToShow=0 end

       declare @amonthbefore integer
       set @amonthbefore=Month(@enddate)-1
           if(@amonthbefore=0)
              begin
                 set @amonthbefore=12
               end         


       if(@amonthbefore  in(1,3,5,7,8,10,12))
           begin
             set @dayToShow=31-DAY(@startdate)+DAY(@enddate)
           end 
       if(@amonthbefore=2)
          begin
            IF (YEAR( @enddate ) % 4 = 0 AND YEAR( @enddate ) % 100 != 0) OR  YEAR( @enddate ) % 400 = 0 
                  begin
                     set @dayToShow=29-DAY(@startdate)+DAY(@enddate)
                   end
            else
                begin
                    set @dayToShow=28-DAY(@startdate)+DAY(@enddate)
            end 
       end
        if(@amonthbefore in (4,6,9,11))
          begin
             set @dayToShow=30-DAY(@startdate)+DAY(@enddate)
          end




  end

else begin --set @monthToShow=convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12)) if(month(@enddate)< month(@startdate)) begin set @monthToShow=12+(month(@enddate)-month(@startdate)) end else begin set @monthToShow= (month(@enddate)-month(@startdate)) end

set @dayToShow=DAY(@enddate)-DAY(@startdate) end

SELECT
FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25) as [yearToShow],
@monthToShow as monthToShow ,@dayToShow as dayToShow , convert(varchar,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25)) +' Year ' + convert(varchar,@monthToShow) +' months '+convert(varchar,@dayToShow)+' days ' as age

return end

Answer 7

Don't you love SQL. Such a simple problem and such complex solutions.