Finding someone's age in SQL Finding someone's age in SQL sql sql

Finding someone's age in SQL

Check out this article: How to calculate age of a person using SQL codes

Here is the code from the article:

DECLARE @BirthDate DATETIMEDECLARE @CurrentDate DATETIMESELECT @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 

There is another way that is a bit simpler:

Select CAST(DATEDIFF(hh, [birthdate], GETDATE()) / 8766 AS int) AS Age

Because the rounding here is very granular, this is almost perfectly accurate. The exceptions are so convoluted that they are almost humorous: every fourth year the age returned will be one year too young if we A) ask for the age before 6:00 AM, B) on the person's birthday and C) their birthday is after February 28th. In my setting, this is a perfectly acceptable compromise.

FWIW, Age can be computed in a straightforward manner without resorting to hacks (not that there's anything wrong with hacks!):

CREATE FUNCTION Age (@BirthDate DATETIME)RETURNS INTASBEGIN    DECLARE @AgeOnBirthdayThisYear INT    DECLARE @BirthdayThisYear DATETIME    SET @AgeOnBirthdayThisYear = DATEDIFF(year, @BirthDate, GETDATE())    SET @BirthdayThisYear = DATEADD(year, @AgeOnBirthdayThisYear, @BirthDate)    RETURN        @AgeOnBirthdayThisYear        - CASE WHEN @BirthdayThisYear > GETDATE() THEN 1 ELSE 0 ENDEND