SQL Server GetAge Function

Matt Pavey

A simple function to calculate age from a date of birth. It handles the two cases that trip people up: a birthday later in the current year, and a birthday later in the current month.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetAge] (@DOB DATETIME, @Today DATETIME)
RETURNS INT
AS
BEGIN

    DECLARE @Age INT

    SET @Age = YEAR(@Today) - YEAR(@DOB)

    -- if the birthday month has not arrived yet, subtract one
    IF MONTH(@Today) < MONTH(@DOB)
    BEGIN
        SET @Age = @Age - 1
    END

    -- if it is the birthday month but the day has not arrived, subtract one
    IF MONTH(@Today) = MONTH(@DOB) AND DAY(@Today) < DAY(@DOB)
    BEGIN
        SET @Age = @Age - 1
    END

    RETURN @Age

END

Usage

DECLARE @Today AS DATETIME

SET @Today = GETDATE()

SELECT ID,
       DOB,
       'Age' = dbo.GetAge(DOB, @Today)
FROM   MyTable