January 26, 2015 at 3:37 am
cfox 61877 (1/23/2015)
I find this works better than a scalar function...
CREATE FUNCTION dbo.AgeInYears
(
@BirthDate DATETIME2,
@AsOfDate DATETIME2
)
RETURNS TABLE
AS
RETURN
(
SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -
CASE WHEN DATEPART(DY, @AsOfDate) < DATEPART(DY, @BirthDate) THEN 1
ELSE 0
END AS Age
);
Then a simple CROSS APPLY in the query...
SELECT I.Name, I.Birthdate, A.Age
FROM Individual AS I
CROSS APPLY AgeInYears(I.Birthdate, CURRENT_TIMESTAMP) AS A
WHERE A.Age >= 18;
seems to be a day out in leap years e.g. @BirthDate = '2001/04/01' , @AsOfDate = '2016/03/31' gives 15 and it should still be 14
January 26, 2015 at 9:42 am
Thanks for catching that, andrew_dale!
Here's the fix...
CREATE FUNCTION dbo.AgeInYears
(
@BirthDate DATETIME2,
@AsOfDate DATETIME2
)
RETURNS TABLE
AS
RETURN
(
SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -
CASE WHEN MONTH(@AsOfDate) < MONTH(@BirthDate) THEN 1
WHEN MONTH(@AsOfDate) = MONTH(@BirthDate) AND
DAY(@AsOfDate) < DAY(@BirthDate) THEN 1
ELSE 0
END AS Age
);
May 11, 2015 at 1:25 pm
Wow, who knew age would generate so many responses.
May 11, 2015 at 2:09 pm
Iwas Bornready (5/11/2015)
Wow, who knew age would generate so many responses.
It is a sensitive subject:-D
😎
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply