Wednesday, August 13, 2008

Simple Age Calculation

I was helping someone with some SQL stuff and needed to give them a function to calculate someone's age.  Rooted around a bunch of places and finally just ended up re-writing it. So this is mostly to have it on hand. This function is needed because DateDiff doesn't check if they've had their birthday this year.

CREATE FUNCTION CalculateAge (
    @Start smalldatetime
    , @End smalldatetime
    )
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Age int

    SET @Age = DateDiff(year, @Start, @End)
    IF DateAdd(year, @Age, @Start) > @End
        SET @Age = @Age - 1

    RETURN @Age

END
GO

VB Version

Public Function Age(ByVal value As Date, ByVal d As Date) As Integer
    Age = DateDiff(DateInterval.Year, value, d)
    If value.AddYears(Age) > d Then Age = Age - 1
    Return Age
End Function