Calculating age in SQL

A number of approaches to calculating age are reported, often relying on artefacts of type conversions or other features that are hard to prove correct in all cases. Beware anything that divides DATEDIFF(DAY, DoB, GETDATE()) by 365.25!

This is a more imperative approach based on:

determining the number of years between birth and now;
then deducting one year if this year's birthday hasn't occurred yet.

SELECT   
  DATEDIFF(YEAR, DoB, GETDATE()) 
    - CASE
        -- birthday before this month
        WHEN DATEPART(MONTH, DoB) < DATEPART(MONTH, GETDATE()) 
        -- birthday today or earlier this month
         OR (DATEPART(MONTH, DoB) = DATEPART(MONTH, GETDATE()) 
             AND DATEPART(DAY, DoB) <= DATEPART(DAY, GETDATE()))
        THEN 0
        ELSE 1
      END AS Age
FROM dbo.Person

A more compact form of the same logic is:

SELECT   
  CASE 
    WHEN DATEPART(MONTH, DateOfBirth) * 100 + DATEPART(DAY, DateOfBirth) 
      <= DATEPART(MONTH, GETDATE()) * 100 + DATEPART(DAY, GETDATE()) 
        THEN DATEDIFF(YEAR, DateOfBirth, GETDATE())
    ELSE DATEDIFF(YEAR, DateOfBirth, GETDATE()) - 1
  END

tsql, functions, age