Sorry for the small misunderstanding but I am using john.arnotts code. The results I mentioned before follow from the following code:
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
I hope this gives a better insight.
Grz,
Robert