# 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```