Selecting someone's age based on a birthdate using Microsoft SQL Server

To calculate someone's age whe use datediff on the birthday and the current datetime, getting the year. Datediff does not look at the month and day, so we use a case to determine if the person has already had his birthday this year and then subtract that number from the datediff result.

Use the query below and experiment with it in the query analyzer. Change the date and see if the result matches.

SELECT DATEDIFF(yy, CONVERT(datetime, '28/10/1976', 103), GETDATE()) -
CASE WHEN DATEPART(m, CONVERT(datetime, '28/10/1976', 103)) < DATEPART(m, getdate())
THEN 0
ELSE CASE WHEN (DATEPART(m, CONVERT(datetime, '28/10/1976', 103)) = DATEPART(m, getdate()))
AND (DATEPART(d, CONVERT(datetime, '28/10/1976', 103)) <= DATEPART(d, getdate()))
THEN 0
ELSE 1 END
END AS age