How to calculate age (in years) based on Date of Birth and getDate()

Ensuring DateTime Format

Before doing any computations, verify that the DOB is in the datetime format. This enforces uniformity and ensures accurate calculations:

SELECT CONVERT(datetime, DOB, 112) as FormattedDOB
FROM YourTable;


This converts the date of birth to our desired format (YYYYMMDD).

Accounting for those Leaplings

Let's not forget about the leap years. We can handle them by using 365.25 days as the denominator, taking into account that extra day every four years:

SELECT FLOOR(DATEDIFF(DAY, DOB, GETDATE()) / 365.25) AS Age
FROM YourTable;


You're welcome, leaplings! The formula does not forget the extra quarter day you add every year.

Those born on Leap Day (Extra Special Case)

For those special folks whose birthdays are on leap day (29th February), we should increment their age on 28th February:

SELECT 
  CASE 
    WHEN DOB = '2000-02-29' AND MONTH(GETDATE()) > 2 THEN 
      FLOOR(DATEDIFF(DAY, DATEADD(DAY, -1, DOB), GETDATE()) / 365.25)
    ELSE
      FLOOR(DATEDIFF(DAY, DOB, GETDATE()) / 365.25)
  END AS Age
FROM YourTable;


This takes care of ages for the rare and awesome humans who only see their actual birthdays every four years!

Building a Reusable Function

We can build a SQL function for frequent use:

CREATE FUNCTION dbo.CalculateAge (@DOB datetime)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
RETURN DATEDIFF(YEAR, @DOB, GETDATE()) - CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END;
END;


You can now calculate age with a single function call:

SELECT dbo.CalculateAge(DOB) AS Age
FROM YourTable;


Who said you can't teach an old function new tricks?

Handling Diverse Date Formats

Sometimes, the DOB might not be in the correct format. We can tackle this by converting it before calculating the age:

SELECT 
  DATEDIFF(YEAR, CONVERT(datetime, DOB, 112), GETDATE()) -
  CASE 
    WHEN (MONTH(CONVERT(datetime, DOB, 112)) > MONTH(GETDATE())) OR 
         (MONTH(CONVERT(datetime, DOB, 112)) = MONTH(GETDATE()) AND DAY(CONVERT(datetime, DOB, 112)) > DAY(GETDATE())) 
      THEN 1 
      ELSE 0 
  END AS Age 
FROM YourTable;


It's like trying to eat a cake with a fork - much easier when you have the right tool!

Testing Edge Cases

Back up your solution's credibility with test cases, especially those edge cases on leap years, or someone who has just been born:

--Checking our function with some edge case birthdays
WITH SampleDates AS (
  SELECT CAST('2000-02-29' AS datetime) AS DOB--Born on a leap day, bad luck.
  UNION ALL SELECT CAST('2001-12-31' AS datetime)--New Year's Eve, party baby!
  UNION ALL SELECT CAST('1995-01-01' AS datetime)--New Year's Day, hangover baby!
)
SELECT DOB, dbo.CalculateAge(DOB) AS CalculatedAge
FROM SampleDates;


Remember, testing is like the icing on the cake - it makes everything better!

Simple Approach

Some prefer only knowing the FullYear of their age:

SELECT 
  (CAST(CONVERT(varchar, GETDATE(), 112) AS INT) - 
   CAST(CONVERT(varchar, DOB, 112) AS INT)) / 10000 AS Age
FROM YourTable;


In computing, just like in life, simplicity can sometimes be the real beauty.