0 Replies - 1708 Views - Last Post: 09 July 2008 - 05:43 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

MSSQL stored procedure for calculating a persons age

Posted 09 July 2008 - 05:43 PM

Description: Pass the procedure the persons birthdate (in DATETIME format) and the current date (in DATETIME format) and the procedure will return the persons ageThis is a MSSQL stored procedure used for calculating a persons age
CREATE FUNCTION dbo.udfFindPersonsAge(@BirthDate DATETIME, @CurDate DATETIME)
RETURNS TINYINT
AS

BEGIN

	DECLARE @YearBorn INT
	DECLARE @CurYear INT
	DECLARE @PersonsAge TINYINT

	SET @YearBorn = DATEPART(yy, @BirthDate)
	SET @CurYear = DATEPART(yy, @CurDate)

	IF DATEPART(m, @BirthDate) = 2 AND DATEPART(d, @BirthDate) = 29
		SET @PersonsAge = 
		CASE
			WHEN ('2/28/' + CONVERT(VARCHAR(4), @CurYear)) < @CurDate THEN
				(SELECT @CurYear - @YearBorn)
			ELSE
				(SELECT (@CurYear -1) - @YearBorn)
		END
	ELSE
		SET @PersonsAge =			
		CASE 
			WHEN (CONVERT(VARCHAR(2), DATEPART(m, @BirthDate)) + '/' 
			+ CONVERT(VARCHAR(2), DATEPART(d, @BirthDate)) + '/' 
			+ CONVERT(VARCHAR(4), @CurYear)) < @CurDate THEN
				(SELECT @CurYear - @YearBorn)
			ELSE
				(SELECT(@CurYear - 1) - @YearBorn)
		END

	RETURN @PersonsAge
END


Is This A Good Question/Topic? 0
  • +

Page 1 of 1