SELECT 
	[day], 
	dob, 
	age = datediff(yy,dob,[day]) -
		case when [day] < dateadd(yy,datediff(yy,dob,[day]), dob) then 1 else 0 end
FROM (
SELECT [day] = '1992-07-07', dob = '1927-04-24' UNION ALL -- 65
SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85 
SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80 
SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85 
SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0
) d

USE [SomeDatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[Age2]    Script Date: 26-1-2013 10:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Robert
-- Create date: 23-01-2013
-- Description:	Calculating Age
-- this function is not always OK
-- =============================================
ALTER FUNCTION [dbo].[Age2]
(
	-- Add the parameters for the function here
	@Birthday As datetime,
	@Day_of_Decease As datetime
)

RETURNS nvarchar(100)
AS
BEGIN
	-- Declare the variables here
Declare @Birth Datetime
	   ,@Death Datetime
       ,@Today Datetime
       ,@AgeYears int
       ,@AgePlusMonths int
       ,@AgePlusDays int	
       ,@LastBD datetime
       ,@LastMonthBD datetime
	   ,@Result nvarchar(100)


--======================--
SET @Birth = @Birthday
SET @Death = @Day_of_Decease
SET @Today = Getdate()
--======================--
-- first part: athlete is still alive
IF @Death Is NULL
  BEGIN
	SET @AgeYears = year(@today)-year(@Birth)       
		- case  when month(@today) < month(@Birth)               
				  or (month(@today) = month(@Birth) and day(@today) < day(@Birth))               
				then 1              
				else 0        
		  end
	SET @LastBD = dateadd(year,@AgeYears,@Birth)       
	SET @AgePlusMonths = datediff(month, @LastBD, @Today)  
		- case  when month(@today) <= month(@LastBD) and day(@today) < day(@LastBD)            
				then 1              
				else 0        
		  end        
		- case  when month(@Birth) = 2 and day(@Birth) = 29
				then 1              
				else 0        
		  end       
	SET @LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD)
	SET @AgePlusDays = datediff(day, @LastMonthBD, @Today)
  END
-- second part: the athlete is deceased
ELSE
  BEGIN
	SET @AgeYears = year(@Death)-year(@Birth)       
		- case  when month(@Death) < month(@Birth)               
				  or (month(@Death) = month(@Birth) and day(@Death) < day(@Birth))               
				then 1              
				else 0        
		  end
	SET @LastBD = dateadd(year,@AgeYears,@Birth)
	SET @AgePlusMonths = datediff(month, @LastBD, @Death)  
		- case  when month(@Death) < month(@LastBD)
				or (month(@Death) = month(@LastBD) and day(@Death) < day(@LastBD))            
				then 1              
				else 0        
		  end        
		- case  when month(@Birth) = 2 and day(@Birth) = 29
				then 1              
				else 0        
		  end       
	SET @LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD)
	SET @AgePlusDays = datediff(day, @LastMonthBD, @Death)
  END

-- third part: Return the result of the function
	SET @Result = CAST(@AgeYears As nvarchar(20)) + 'yr ' + 
	CAST(@AgePlusMonths As nvarchar(20)) + 'mnth '+ CAST(@AgePlusDays As nvarchar(20)) + 'days'
	RETURN @Result

END

SELECT 
	dob, 
	[day], 
	DATEDIFF(yy, 0, [day] - dob) Years,
	DATEDIFF(MM, 0, [day] - dob)%12 Months,
	DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, 0, [day] - dob), 0), [day] - dob) days
	
FROM (
SELECT [day] = CONVERT(datetime, '1992-07-07', 120), dob =  CONVERT(datetime, '1927-04-24', 120) UNION ALL -- 65
SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85 
SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80 
SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85 
SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0
) d

SELECT 
	dob, 
	[day], 
	YEAR([day] - dob)-1900 Years,
	MONTH([day] - dob)-1 Months,
	DAY([day] - dob)-1 days
	
FROM (
SELECT [day] = CONVERT(datetime, '1992-07-07', 120), dob =  CONVERT(datetime, '1927-04-24', 120) UNION ALL -- 65
SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85 
SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80 
SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85 
SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0
) d