View Single Post
  #2 (permalink)  
Old February 28th, 2006, 10:37 AM
rgerald rgerald is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SQL Server works better when dates, including birthdates, are stored as datetime data types instead of varchar.

Once you have the birth dates stored as datetime data, the following function can be used to calculate age:

Code:
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_AgeCalc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_AgeCalc]
GO




CREATE FUNCTION [dbo].[f_AgeCalc] (
@DateOfBirth DateTime = NULL,
@RefDate DateTime = NULL)
RETURNS int AS  
BEGIN
RETURN (SELECT CASE
WHEN ((@DateOfBirth IS NULL) OR (@RefDate IS NULL))
THEN NULL
WHEN (@DateOfBirth > @RefDate) THEN NULL
WHEN DateADD(yyyy, DateDiff(yyyy, @DateOfBirth, @RefDate), @DateOfBirth) > @RefDate
THEN DateDiff(yyyy, @DateOfBirth, @RefDate) - 1
ELSE DateDiff(yyyy, @DateOfBirth, @RefDate) 
END)
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Call the function as:

SELECT dbo.f_CalcAge(BirthDt, GetDate()) [Age]

Rand
Reply With Quote