-- select dbo.FN_CalculateAge('1/21/1990') doB -- DD/MM/YYYY.
Create FUNCTION [dbo].[FN_CalculateAge](@dayOfBirth datetime)
RETURNS Varchar(300)
AS
BEGIN
DECLARE @Age varchar(300)
DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int
SELECT @today = GETDATE()
SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)
SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)
SELECT @months = MONTH(@today - @thisYearBirthDay) - 1
SELECT @days = DAY(@today - @thisYearBirthDay) - 1
Set @Age=Cast(@years As Varchar)+' years '+ Cast(@months As Varchar)+' months '+Cast(@days As Varchar) +' days'
RETURN @Age
END
Create FUNCTION [dbo].[FN_CalculateAge](@dayOfBirth datetime)
RETURNS Varchar(300)
AS
BEGIN
DECLARE @Age varchar(300)
DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int
SELECT @today = GETDATE()
SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)
SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)
SELECT @months = MONTH(@today - @thisYearBirthDay) - 1
SELECT @days = DAY(@today - @thisYearBirthDay) - 1
Set @Age=Cast(@years As Varchar)+' years '+ Cast(@months As Varchar)+' months '+Cast(@days As Varchar) +' days'
RETURN @Age
END
No comments:
Post a Comment