SQL advanced -> Calculate correct age in MS-Access query

If we know Birth date of employee, we can calculate his/her age using function DateDiff("yyyy",DateOfBirth,Date()). But this simple formula will not allways give us correct results. If Birth day did not passed yet in the current year, we will recive Age bigger by 1. In order to overcome this trouble, we need to improve the formula. Can do this by substructing 1 year from the result if Birth day did not take place yet. Current year Birth date can be constructed in this manner:

DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))

Expression:

Date() < DateSerial(Year( Date()),Month(DateOfBirth), Day(DateOfBirth)))

will return (-1) if Current Date is smaller than the Birth Date of this year.

SELECT FirstName, LastName, DateOfBirth,
DateDiff ("yyyy",DateOfBirth, Date())+(Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS Age,
Date() < DateSerial(Year( Date()), Month(DateOfBirth), Day(DateOfBirth))) AS AddYear
FROM Employees;

The results will look like this:
FirstNameLastNameDateOfBirthAgeAddYear
johnsilver08/12/5849-1
gretgarbo15/01/49590
lechen01/10/6938-1
danieldefo01/04/78300
maggiforth25/02/52560
voichlutz30/01/82260



sqlexamples.info