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 sql server developers programmers may need how many years passed between two dates just the same case while calculating the age of a person within sql query/queries.
And this may be a problem in some situations if you have missed some things that may cause you fall in error or did not make enough tests to figure out tricky situations.
Here is the sql code 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 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 we will calculate the age of person.
In order to code in a good sql server development process, we can move sql query codes in sql server user defined functions. Just, create a user-defined function to get use of this t-sql code in our
orher 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, thedbo.fn_CalculateAge
function will return 0.
You can use the below syntax while calling the CalculateAge function.
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
 BlinkList  Del.icio.us  Digg  Furl  Simpy  Spurl  DZone  ma.gnolia  Shadows
|