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