SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014

How to Calculate Age of a Person using SQL Codes


You can easily calculate the age of a person by using only a line of sql codes.
As MS SQL Server developers or programmers may need how many years passed between two dates just the same case while calculating the age of a person using sql query or sql queries.
And this may be a problem in some situations if you have missed some critical point that may cause you fall in error or did not make enough tests to figure out tricky situations.

Here is the sql codes for getting the age of a person :

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = '20070210', @BirthDate = '19790519'

SELECT
  DATEDIFF(YY, @BirthDate, @CurrentDate) -
  CASE
    WHEN(
      (MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
      (MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
    ) THEN 1
    ELSE 0
  END

For this age calculation two parameters are required, the @BirthDate parameter is for the persons date of birth and the @CurrentDate parameter is for the date at which the t-sql codes will calculate the age of person.





In order to code in an optimized MS SQL Server development process, as sql developers or SQL Server Administrators we can move sql query codes in SQL Server as user-defined functions.
Just, create a user-defined function to get use of this t-sql code in our other sql batch codes or stored procedures to get a more readable sql code.


CREATE FUNCTION dbo.fn_CalculateAge
(
  @BirthDate datetime,
  @CurrentDate datetime
)
RETURNS int

AS

BEGIN

IF @BirthDate > @CurrentDate
  RETURN 0

DECLARE @Age int
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) -
  CASE WHEN(
    (MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
    (MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
  ) THEN 1 ELSE 0 END

RETURN @Age

END

GO

Note that this user defined sql function takes care of negative values that may occur because of fault date parameters entry. If you enter a date earlier than the birthdate of the person for age calculation, the dbo.fn_CalculateAge t-sql user define function will return 0.

You can use the below syntax while calling the CalculateAge function in your t-sql codes.

SELECT dbo.fn_CalculateAge('20010210', GETDATE())

But if you actually deal with how many years have passed between two dates,

If you do not want to check the date order while passing the date parameters to the function or you really deal with how many years have passed between two dates instead of an aged calculation, you can use the below script for creating the fn_CalculateAge sql function.

ALTER FUNCTION dbo.fn_CalculateAge
(
  @BirthDate datetime,
  @CurrentDate datetime
)
RETURNS int

AS

BEGIN

IF @BirthDate > @CurrentDate
BEGIN
  DECLARE @TempDate datetime
  SELECT
    @TempDate = @BirthDate,
    @BirthDate = @CurrentDate,
    @CurrentDate = @TempDate
END

DECLARE @Age int
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) -
  CASE WHEN(
    (MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
    (MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
  ) THEN 1 ELSE 0 END
RETURN @Age

END

GO





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems