Friday, January 11, 2008

SQL Server GetAge Function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
GO

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

BEGIN
    
-- declare variables
    
DECLARE @Age
INT

     -- set defaults
    
SET @Age = Year(@Today) - Year(@DOB)

     -- if it is a previous month subtract one off the age
    
IF Month(@Today) < Month(@DOB)
    
BEGIN
         
SET @Age = @Age -1
     END

     -- if it is the current month but a previous day subtract one off the age
    
IF Month(@Today) = Month(@DOB) AND Day(@Today) < Day(@DOB
)
    
BEGIN
          SET @Age = @Age - 1
     END
 

     -- return value
    
RETURN @Age
END

Usage

-- declare variables
DECLARE @Today AS DATETIME

-- set default values
SET @Today = GetDate()

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

0 comments: